Ranking Alphabetically

[Guest]

I'll use a simple example...

I have 5 words I need to rank in ascending order, alphabetically.

Carrot
Peach
Apple
Salad
Banana


I would like the rank function to produce the following results:

Rank Name
------ -------
1 Apple
2 Banana
3 Carrot
4 Peach
5 Salad

How please? Many thanks.

 

[Domenic]

Assuming that A1:A5 contains your data, try...

B1, copied down:

=INDEX($A$1:$A$5,MATCH(SMALL(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),ROWS($B$1:B
1)),COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

C1, copied down:

=COUNTIF($B$1:$B$5,"<"&B1)+1

Hope this helps!

 

[Bernie Deitrick]

You could simply sort the column ascending.

If your values are in a1:a5, then the formula

=SUMPRODUCT((A1>$A$1:$A$5)*1)+1

copied down for 5 rows will give the ranking.

 

[Guest]

Bernie,
The formula you have suggested is extremely elegant (in the way it works).
Regards,
B. R. Ramachandran

 

[Bernie Deitrick]

B. R.,

Thanks. I would like to take credit for it, but I learned that technique a looooong time ago from
people who are much smarter <vbg>

Bernie
MS Excel MVP

 

[Guest]

Bernie,
This is a follow-up of my previous response.
If there is a tie, (e.g., banana, apple, banana, grapes), the formula would
rank them 2,1,2,and 4 respectively. I am curious, how you would modify the
formula to make grapes rank 3 (and not 4)?
Regards,
B.R. Ramachandran

 

[Bernie Deitrick]

I'm not aware of a technique using a single worksheetfunction that can do that - I would write a
User-Defined-Function, or use multiple columns of formulas.

HTH,
Bernie
MS Excel MVP

 

[Domenic]

Assuming that A1:A4 contains your data, try...

B1, copied down:

=SUM(IF(A1>$A$1:$A$4,1/COUNTIF($A$1:$A$4,$A$1:$A$4)))+1

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Bernie,
That's what I thought.
Thanks for your prompt response.
B.R.Ramachandran

 

[Guest]

Hello,

a stable sort which would leave identical items in it's original order (I
assume the data in column A):

B1:

=COUNTIF($A:$A,"<"&A1)+COUNTIF($A$1:A1,A1)

C1:

=INDEX(A:A,MATCH(ROW(),B:B,FALSE))

Copy down...

Regards,
Bernd

 

[Guest]

Excellent. The 1/COUNTIF idea is very clever.
B.R. Ramachandran

 

[Guest]

If the list is in A1 to A7 I would have in B1:
=SUMPRODUCT(--($A$1:$A1=$A1))
and in B2
=SUMPRODUCT(--($A$1:$A$7<=$A1),--($B$1:$B$7=1))
Then copy to the seventh row.

 

[Guest]

Thank you Bernie, your formula worked perfectly for my simple example.
However, my apologies, I now realize I should have mentioned that my real
data set contains quite a few blank cells in the column of data I am trying
to rank (see revised example below). I need to exclude the blank cells from
the rankings, so can I ask you to offer an enhance formula to accomplish
this? Many thanks.


A1: Carrot
A2: Peach
A3:
A4: Apple
A5: Salad
A6:
A7: Banana


I would like the rank function to produce the following results:

Rank Name
------ -------
1 Apple
2 Banana
3 Carrot
4 Peach
5 Salad

 

[Bernie Deitrick]

In B1, use the formula

=IF(A1="","",SUMPRODUCT((A1>$A$1:$A$7)*($A$1:$A$7<>""))+1)

and copy down to match your list.

HTH,
Bernie
MS Excel MVP