# Sorting and giving unique values in excel

Posted on

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.

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.