Ranking Strings

[Amateurhr]

There's a vba function called Application.Rank which can tell me the rank of a number amongst a range of other numbers.

Is there a corresponding function that can do this with alpha strings?

I want to rank something like this:

{ Ralph, Amanda, Chester }

and have that give me the following rank:

{ 3, 1, 2 }

 

[jproffer]

Assuming you mean Ranking alphebetically, and if you can live with a helper column (which could be hidden, naturally), you could do something like this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Ralph</TD><TD style="TEXT-ALIGN: right">82</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Amanda</TD><TD style="TEXT-ALIGN: right">65</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Chester</TD><TD style="TEXT-ALIGN: right">67</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="COLOR: #ffffff">………………</TD><TD style="COLOR: #ffffff">………………</TD><TD style="COLOR: #ffffff">………………</TD><TD style="COLOR: #ffffff">………………</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=CODE(LEFT(A1,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=RANK(B1,$B$1:$B$3,1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=CODE(LEFT(A2,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=RANK(B2,$B$1:$B$3,1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">=CODE(LEFT(A3,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=RANK(B3,$B$1:$B$3,1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[T. Valko]

There's a vba function called Application.Rank which can tell me the rank of a number amongst a range of other numbers.

Is there a corresponding function that can do this with alpha strings?

I want to rank something like this:

{ Ralph, Amanda, Chester }

and have that give me the following rank:

{ 3, 1, 2 }

Like this...

Book1
	A	B
2	Ralph	3
3	Amanda	1
4	Chester	2
Sheet1

This formula entered in B2 and copied down:

=COUNTIF(A$2:A$4,"<"&A2)+1

 

[Amateurhr]

Thank you, I used Application.Countif and everything works great

 

[T. Valko]

Thank you, I used Application.Countif and everything works great

You're welcome. Thanks for the feedback!