Auto Sort List With Formula

[avendi]

Hi all,

I am trying to remember how to auto sort a dynamic list, as attached. I seem to recall doing this once before with index, match, rows, etc functions. My data shows the data as current and as preferred. I hoped to find a formula to enter that I can copy down to produce the results shown.

Cheers,
Avendi

[azumi]

For Item, put this on E5 and ENTERED as array formula then copied down:

=IFERROR(INDEX($A$5:$A$24,SMALL(IF($B$5:$B$24=$F5,ROW($A$5:$A$24)-ROW($A$5)+1),COUNTIF($F$5:F5,F5))),"")

For Total, put this on F5 and ENTERED as array formula then dopied down:

=INDEX($B$5:$B$24,MATCH(LARGE(COUNTIF($B$5:$B$24,"<"&$B$5:$B$24),ROW(1:1)),COUNTIF($B$5:$B$24,"<"&$B$5:$B$24),0))

[avendi]

Thanks Azumi!