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.

  • Hello Sakira, the data in your third example bears little resemblance to the first two. How is it derived? – Paul Jun 14 '12 at 13:38
  • It almost looks like he is applying a "DENSERANK" to the dataset in the 2nd example? – F106dart Jun 14 '12 at 13:47
  • @Paul: I think she wants to replace the elements of the column with natural numbers, whatever is the starting number: so the first value of the series will always be one, the second two, and so on, but preserving the number of occurrences for each value – clabacchio Jun 14 '12 at 14:38
  • @clabacchio I think so too but we need Sarika to confirm. – Paul Jun 15 '12 at 0:28

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.