|
Ranking Alphabetically
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. |
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! In article , cdavidson wrote: 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. |
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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
Bernie,
The formula you have suggested is extremely elegant (in the way it works). Regards, B. R. Ramachandran "Bernie Deitrick" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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 Bernie, The formula you have suggested is extremely elegant (in the way it works). Regards, B. R. Ramachandran "Bernie Deitrick" wrote: 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 |
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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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 "B. R.Ramachandran" wrote in message ... 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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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! In article , "B. R.Ramachandran" wrote: 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,
That's what I thought. Thanks for your prompt response. B.R.Ramachandran "Bernie Deitrick" wrote: 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 "B. R.Ramachandran" wrote in message ... 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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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 -- Excel 2002 |
Excellent. The 1/COUNTIF idea is very clever.
B.R. Ramachandran "Domenic" wrote: 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! In article , "B. R.Ramachandran" wrote: 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 |
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. "B. R.Ramachandran" wrote: 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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
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 "cdavidson" wrote in message ... 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" wrote: 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. -- HTH, Bernie MS Excel MVP "cdavidson" wrote in message ... 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. |
| All times are GMT +1. The time now is 03:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com