How can I change rows that appeared more than once to missing rows

Please I need a help!!

I have the set of vectors below. In the second column, 5.0000 and 6.0000 appeared twice, and between 9.0000 and 12.0000, 10.0000 and 11.0000 are missing.

Please how can I change the rows with 5.0000 and 6.0000 to 10.0000 and 11.0000 that are missing and sort the second column in ascending with respect to their corresponding rows.

Please, I need a solution that is applicable to related problems.

2005.00      1.00000     0.000000     0.000000     0.000000
2005.00      2.00000     0.000000     0.000000     0.000000
2005.00      3.00000     0.000000     0.000000     0.000000
2005.00      4.00000     0.000000     0.000000     0.000000
2005.00      5.00000     0.000000     0.000000     0.000000
2005.00      6.00000     0.000000     0.000000     0.000000
2005.00      5.00000      56.0000      4464.00      1.25448
2005.00      6.00000      2937.00      4320.00      67.9861
2005.00      7.00000      2974.00      4464.00      66.6219
2005.00      8.00000      2097.00      4464.00      46.9758
2005.00      9.00000      797.000      4320.00      18.4491
2005.00      12.0000      191.000      4464.00      4.27867

This is my desired output

2005.00      1.00000     0.000000     0.000000     0.000000
2005.00      2.00000     0.000000     0.000000     0.000000
2005.00      3.00000     0.000000     0.000000     0.000000
2005.00      4.00000     0.000000     0.000000     0.000000
2005.00      5.00000      56.0000      4464.00      1.25448
2005.00      6.00000      2937.00      4320.00      67.9861
2005.00      7.00000      2974.00      4464.00      66.6219
2005.00      8.00000      2097.00      4464.00      46.9758
2005.00      9.00000      797.000      4320.00      18.4491
2005.00      10.00000     0.000000     0.000000     0.000000
2005.00      11.00000     0.000000     0.000000     0.000000
2005.00      12.0000      191.000      4464.00      4.27867

Thank you in advance.

[Dyuman Joshi]

If you have to work with a specific case -

y=[2005.00      1.00000     0.000000     0.000000     0.000000
2005.00      2.00000     0.000000     0.000000     0.000000
2005.00      3.00000     0.000000     0.000000     0.000000
2005.00      4.00000     0.000000     0.000000     0.000000
2005.00      5.00000     0.000000     0.000000     0.000000
2005.00      6.00000     0.000000     0.000000     0.000000
2005.00      5.00000      56.0000      4464.00      1.25448
2005.00      6.00000      2937.00      4320.00      67.9861
2005.00      7.00000      2974.00      4464.00      66.6219
2005.00      8.00000      2097.00      4464.00      46.9758
2005.00      9.00000      797.000      4320.00      18.4491
2005.00      12.0000      191.000      4464.00      4.27867];
y([5 6],2)=[10;11];
[~,i]=sort(y(:,2));
y=y(i,:)

y = 12×5

1.0e+03 * 2.0050 0.0010 0 0 0 2.0050 0.0020 0 0 0 2.0050 0.0030 0 0 0 2.0050 0.0040 0 0 0 2.0050 0.0050 0.0560 4.4640 0.0013 2.0050 0.0060 2.9370 4.3200 0.0680 2.0050 0.0070 2.9740 4.4640 0.0666 2.0050 0.0080 2.0970 4.4640 0.0470 2.0050 0.0090 0.7970 4.3200 0.0184 2.0050 0.0100 0 0 0

[TTA]

Thank you very much. But I am looking for a solution where the duplicate and the missing values in column 2 are not known. It can just be any case. for example,

Below is another case

2022.00 1.00000 0.000000 0.000000 0.000000

2022.00 2.00000 0.000000 0.000000 0.000000

2022.00 3.00000 0.000000 0.000000 0.000000

2022.00 1.00000 4435.00 4464.00 99.3504

2022.00 2.00000 3718.00 4032.00 92.2123

2022.00 3.00000 4073.00 4464.00 91.2410

2022.00 4.00000 4073.00 4320.00 94.2824

2022.00 5.00000 3776.00 4464.00 84.5878

2022.00 6.00000 2714.00 4320.00 62.8241

2022.00 7.00000 608.000 4464.00 13.6201

2022.00 8.00000 3367.00 4464.00 75.4256

2022.00 9.00000 1872.00 4320.00 43.3333

and this is what I expected to have

2022.00 1.00000 4435.00 4464.00 99.3504

2022.00 2.00000 3718.00 4032.00 92.2123

2022.00 3.00000 4073.00 4464.00 91.2410

2022.00 4.00000 4073.00 4320.00 94.2824

2022.00 5.00000 3776.00 4464.00 84.5878

2022.00 6.00000 2714.00 4320.00 62.8241

2022.00 7.00000 608.000 4464.00 13.6201

2022.00 8.00000 3367.00 4464.00 75.4256

2022.00 9.00000 1872.00 4320.00 43.3333

2022.00 10.00000 0.000000 0.000000 0.000000

2022.00 11.00000 0.000000 0.000000 0.000000

2022.00 12.00000 0.000000 0.000000 0.000000

Do you understand?

[Dyuman Joshi]

In this case, all empty rows are sent to the bottom, however in the earlier example there was still a row after them.

Please clarify this ambiguity and say what exactly do you want to do with the empty rows.

Answer 1

[dpb]

A=[
2005.00      1.00000     0.000000     0.000000     0.000000
2005.00      2.00000     0.000000     0.000000     0.000000
2005.00      3.00000     0.000000     0.000000     0.000000
2005.00      4.00000     0.000000     0.000000     0.000000
2005.00      5.00000     0.000000     0.000000     0.000000
2005.00      6.00000     0.000000     0.000000     0.000000
2005.00      5.00000      56.0000      4464.00      1.25448
2005.00      6.00000      2937.00      4320.00      67.9861
2005.00      7.00000      2974.00      4464.00      66.6219
2005.00      8.00000      2097.00      4464.00      46.9758
2005.00      9.00000      797.000      4320.00      18.4491
2005.00      12.0000      191.000      4464.00      4.27867];

% engine
d=setdiff([1:size(A,1)].',A(:,2));
n=histc(A(:,2),unique(A(:,2)));
A(n>1,2)=d;
A=sortrows(A,[1:2]);
format bank     % to prettify output only
disp(A)

       2005.00          1.00             0             0             0
       2005.00          2.00             0             0             0
       2005.00          3.00             0             0             0
       2005.00          4.00             0             0             0
       2005.00          5.00         56.00       4464.00          1.25
       2005.00          6.00       2937.00       4320.00         67.99
       2005.00          7.00       2974.00       4464.00         66.62
       2005.00          8.00       2097.00       4464.00         46.98
       2005.00          9.00        797.00       4320.00         18.45
       2005.00         10.00             0             0             0
       2005.00         11.00             0             0             0
       2005.00         12.00        191.00       4464.00          4.28

[TTA]

Thank you very much

[TTA]

@dbp what an excellent job!!!.......Thank you once again

[dpb]

Glad to help -- appreciate the feedback.