Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .