Sorting

Occasional Visitor

What formula to sort an array within a worksheet, with one of the array columns as Sort criteria.

Example:

Double1.83M232
Double1.86M312
Double2.08M432
Double2.34M632
Single11.41M53.
Single21.65M13.

Sort this 6-row array so that column C is in ascending order (M1,M2,M3,M4,M5,M6)

Alan

2 Replies

@AlanSnow 

 

Assume the table is in A1:E6. In cell G1, enter (hit Ctrl+Shift+Enter after keying/copying to the formula bar):

 

=IFERROR(INDEX(A$1:A$6,MATCH(SMALL(COUNTIF($C$1:$C$6,"<"&$C$1:$C$6),ROWS(G$1:G1)),COUNTIF($C$1:$C$6,"<"&$C$1:$C$6),0)),"N/A")

 

Then, copy down and across.

@AlanSnow 

Depends on which version of Excel you are. If on one with dynamic arrays, that's

image.png