QUESTION :
Can anybody help me for following problem:
If we have 5 rows and 3 columns as
1 5 6
5 4 3
7 1 3
5 2 1
6 1 2
We sort this as follows:
1 1 1
5 1 2
5 2 3
6 5 3
7 4 6
Now I need that for each column, the unique values should be leveled uniquely in ascending order as:
1 1 1
2 1 2
2 2 3
3 3 3
4 4 4
Even sorting with unique levels will do for single column.
ANSWER :
I assume your data is somewhere in your sheet, and I choose to start at B2.
If your original data is in B1:D6
For the first sort use this formula:
B8: =SMALL(B$2:B$6,ROW(B1))
copy this formula to B8:D12
(it looks like the last two values in your 2nd example data are not in order, why?)
Attention: I’m using the first rows of the sheet for numbering, their values has not importance for the above formula. Wherever your data is, the ROWS
function should point the row number 1 with any column.
Now, for the unique values advance use this function:
B14: =IF(B8=B7,B13,B13+1)
copy this formula to B14:D18
Attention: I’m using the cell B7 and B13 knowing they are empty. Wherever you copy the system, the lines above the manipulated data better to be empty.