Sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-1724316%22%20slang%3D%22en-US%22%3ESorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724316%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20formula%20to%20sort%20an%20array%20within%20a%20worksheet%2C%20with%20one%20of%20the%20array%20columns%20as%20Sort%20criteria.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CTABLE%20width%3D%22329%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2281%22%3EDouble%3C%2FTD%3E%3CTD%20width%3D%2262%22%3E1.83%3C%2FTD%3E%3CTD%20width%3D%2262%22%3EM2%3C%2FTD%3E%3CTD%20width%3D%2262%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2262%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDouble%3C%2FTD%3E%3CTD%3E1.86%3C%2FTD%3E%3CTD%3EM3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDouble%3C%2FTD%3E%3CTD%3E2.08%3C%2FTD%3E%3CTD%3EM4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDouble%3C%2FTD%3E%3CTD%3E2.34%3C%2FTD%3E%3CTD%3EM6%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingle1%3C%2FTD%3E%3CTD%3E1.41%3C%2FTD%3E%3CTD%3EM5%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingle2%3C%2FTD%3E%3CTD%3E1.65%3C%2FTD%3E%3CTD%3EM1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E.%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ESort%20this%206-row%20array%20so%20that%20column%20C%20is%20in%20ascending%20order%20(M1%2CM2%2CM3%2CM4%2CM5%2CM6)%3C%2FP%3E%3CP%3EAlan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1724316%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1724533%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813267%22%20target%3D%22_blank%22%3E%40AlanSnow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssume%20the%20table%20is%20in%20A1%3AE6.%20In%20cell%20G1%2C%20enter%20(hit%20Ctrl%2BShift%2BEnter%20after%20keying%2Fcopying%20to%20the%20formula%20bar)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(A%241%3AA%246%2CMATCH(SMALL(COUNTIF(%24C%241%3A%24C%246%2C%22%26lt%3B%22%26amp%3B%24C%241%3A%24C%246)%2CROWS(G%241%3AG1))%2CCOUNTIF(%24C%241%3A%24C%246%2C%22%26lt%3B%22%26amp%3B%24C%241%3A%24C%246)%2C0))%2C%22N%2FA%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20copy%20down%20and%20across.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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.

Highlighted

@AlanSnow 

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

image.png