How to sort table using function/equation only

Question

How can I sort a table in sheet 1 like

A B C D E
3 7 3 6 5

into another table in sheet 2

A C E D B
3 3 5 6 7

by using function only?

Answer 1

One really easy way to do it would be to just have a rank index and then use HLOOKUP to find the corresponding values:

=RANK(A4,$A$4:$E$4,1)

=IF(COUNTIF($A$1:A$1,A1)>1,RANK(A4,$A$4:$E$4,1)+COUNTIF($A$1:A$1,A1)-1,RANK(A4,$A$4:$E$4,1))

=HLOOKUP(COLUMN(),$A$2:$E$4,2,FALSE)

=HLOOKUP(COLUMN(),$A$2:$E$4,3,FALSE)

Answer 2

I think an easier solution is to use the "Large" function:

The Column functions are just an easy way to count from 5 down to 1, but a helper column may be even easier.

You can have a similar answer using the "Small" function as well.

Answer 3

Okay, here's the "one formula does it all" solution without additional temporary columns:

Formula in A6:

=INDEX($A$2:$E$2,MATCH(SMALL($A$3:$E$3+COLUMN($A$3:$E$3)/100000000,COLUMN()),$A$3:$E$3+COLUMN($A$3:$E$3)/100000000,0))

Enter it as an array formula, i.e. press Ctrl-Shift-Enter. Then copy it to the adjacent columns.

To get also the number, use this formula in A7 (again as array formula):

=ROUND(SMALL($A$3:$E$3+COLUMN($A$3:$E$3)/100000000,COLUMN()),6)

Both formulas are a bit bloated as they have to also handle the potential duplicates. The solution is to simply add a very tiny fraction of the column before applying the sorting (SMALL function) - and then to remove it again...

Answer 4

Although I have used the "add a small number technique", I think this is the most elegant for a helper row/column:

=RANK(B4,$B$4:$F$4,0) + COUNTIF($B$4:$F$4,B4)-1

(copy across the row column) RANK gets you close and the COUNTIF portion handles duplicates by counting the number of duplicates of the cell to that point. Since there is always match (itself) you subtract 1 for the final rank. This "sorts" ties in the order that they appear. Note that an empty cell will generate #N/A and character data as #Values.