How to sort table using function/equation only

excel sorting equation
Season picture Season · Feb 21, 2013 · Viewed 55.6k times · Source

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

Stepan1010 picture Stepan1010 · Feb 21, 2013

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)

Example 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))

Example 2

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

Example 3

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

Example 4

Related questions

Sorting Worksheet data by column values using Excel VBA

I have next userform developed in vba, which takes info from a worksheet for displaying info I want to order all the info aphabetically by a Segment, this is the code: Function llenarDatosTabla() Dim vList As Variant Dim ws As …

Read More
Excel VBA - Apply auto filter and Sort by specific colour

I have an auto-filtered range of data. The auto filter was created by the following VB code: Sub Colour_filter() Range("A4").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=…

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More