Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Today I faced the challenge of having to find the rank of a text string in a range of text strings. Or put another way, in what position would the entry sit alphabetically?
The RANK function only works on numeric values. So instead, we have to resort to logic.
=COUNTIF(A1:A5,"<="&A1)
This will figure out how many cells within the range A1:A5 are less than or equal to A1, the value that we're trying to rank.
A beautiful yet simple piece of logic, learned from "anhn" in VB Forums.
This does not work if Text are in Duplicated
For small lists with, just add a space to the end of one of the duplicated pieces of text. This works great. It would not however be practical for large lists with many duplicates.
A slight variation of the above formula will give you a true ranking, even if there are duplicate entries:
=COUNTIF(A1:A5,"<"&A1)+1 However, if you sorting, the following works better: =COUNTIF(A$1:A$5,"<"&A3)+1+IF(COUNTIF(A$1:A1,A1)>1,COUNTIF(A$1:A1,A1)-1,0)
Not as elegant, but it works by counting the number of duplicate entries above the current row and adding that number back to the rank, so it becomes not a true ranking but a sorting.