As with numbers you can sort using the function LARGE, is there a way you can do this alphabetically ??
ADVERTISEMENT
Here's another way...
Assuming that A2:A10 contains the data...
B2:
=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))
B3, copied down:
=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")
Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
ADVERTISEMENT
Here's another way...
Assuming that A2:A10 contains the data...
B2:
=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))
B3, copied down:
=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")
Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
Some notes on the above formula system:
1] It cannot cope with numbers (both pos and neg) in the range to sort.
2] It eliminates duplicates under the assumption of like behavior as Data|Sort > Descending.
3] Although it's less volatile than the system in the link I quoted and it has lesser usage of cell memory, Full Calc and Microseconds per formula is higher.
N.B. I succeeded to eliminate the range housing rankings with Rank from the system in the link I quoted, the revised system becomes less volatile but Microseconds per formula doubles and it cannot cope with neg numbers in the range to sort.
Here's another way...
Assuming that A2:A10 contains the data...
B2:
=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))
B3, copied down:
=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")
Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
Some notes on the above formula system:
1] It cannot cope with numbers (both pos and neg) in the range to sort.
I was under the impression that the range would contain only alpha-characters, but of course I could be wrong.
2] It eliminates duplicates under the assumption of like behavior as Data|Sort > Descending.
Yes, for some reason I was thinking unique values, hence my follow-up post...
3] Although it's less volatile than the system in the link I quoted and it has lesser usage of cell memory, Full Calc and Microseconds per formula is higher.
Interesting, although I wasn't aware that it was at all volatile. I don't see any volatile functions, unless I'm missing something. Can you please shed some light?