|
multiple ranks in one list
I am trying to rank a lengthy list of data (about 3500 rows) with a need to
rank data as the state name chages so a list that looks like this: Alabama Echo 573,308 Alabama Tango 297,498 Alabama Delta 101,623 Alabama Charlie 93,917 Alabama Sierra 93,003 Alabama Gamma 64,818 Alabama Lima 60,336 Arizona Tango 58,392 Arizona Romeo 50,037 Arizona Charlie 36,424 Arizona Lima 32,815 Alaska Lima 32,513 Alaska Tango 31,000 Alaska Gamma 24,905 Alaska Sierra 24,190 Would rank like this: Alabama Echo 573,308 1 Alabama Tango 297,498 2 Alabama Delta 101,623 3 Alabama Charlie 93,917 4 Alabama Sierra 93,003 5 Alabama Gamma 64,818 6 Alabama Lima 60,336 7 Arizona Tango 58,392 1 Arizona Romeo 50,037 2 Arizona Charlie 36,424 3 Arizona Lima 32,815 4 Alaska Lima 32,513 1 Alaska Tango 31,000 2 Alaska Gamma 24,905 3 Alaska Sierra 24,190 4 And so on. I can sort the master list with elementary Excel functions, but am stumped on a formula that I can place in an adjacent column and replicate to the end of the rows to do what I want. Thanks in advance for any suggestions. |
multiple ranks in one list
Assuming that A1:C15 contains the data, try...
D1, copied down: =SUMPRODUCT(--($A$1:$A$15=A1),--(C1<$C$1:$C$15))+1 Hope this helps! In article , spndoc wrote: I am trying to rank a lengthy list of data (about 3500 rows) with a need to rank data as the state name chages so a list that looks like this: Alabama Echo 573,308 Alabama Tango 297,498 Alabama Delta 101,623 Alabama Charlie 93,917 Alabama Sierra 93,003 Alabama Gamma 64,818 Alabama Lima 60,336 Arizona Tango 58,392 Arizona Romeo 50,037 Arizona Charlie 36,424 Arizona Lima 32,815 Alaska Lima 32,513 Alaska Tango 31,000 Alaska Gamma 24,905 Alaska Sierra 24,190 Would rank like this: Alabama Echo 573,308 1 Alabama Tango 297,498 2 Alabama Delta 101,623 3 Alabama Charlie 93,917 4 Alabama Sierra 93,003 5 Alabama Gamma 64,818 6 Alabama Lima 60,336 7 Arizona Tango 58,392 1 Arizona Romeo 50,037 2 Arizona Charlie 36,424 3 Arizona Lima 32,815 4 Alaska Lima 32,513 1 Alaska Tango 31,000 2 Alaska Gamma 24,905 3 Alaska Sierra 24,190 4 And so on. I can sort the master list with elementary Excel functions, but am stumped on a formula that I can place in an adjacent column and replicate to the end of the rows to do what I want. Thanks in advance for any suggestions. |
multiple ranks in one list
Domenic - Thanks, this is very close, however the value 1 is being
replicated. When it ranks it gives a result similar to the following: 1 1 2 3 4 5 1 1 2 3 And so on. Again very close, but not sure how to prevent the number 1 from replicating on the first two lines of each state. "Domenic" wrote: Assuming that A1:C15 contains the data, try... D1, copied down: =SUMPRODUCT(--($A$1:$A$15=A1),--(C1<$C$1:$C$15))+1 Hope this helps! In article , spndoc wrote: I am trying to rank a lengthy list of data (about 3500 rows) with a need to rank data as the state name chages so a list that looks like this: Alabama Echo 573,308 Alabama Tango 297,498 Alabama Delta 101,623 Alabama Charlie 93,917 Alabama Sierra 93,003 Alabama Gamma 64,818 Alabama Lima 60,336 Arizona Tango 58,392 Arizona Romeo 50,037 Arizona Charlie 36,424 Arizona Lima 32,815 Alaska Lima 32,513 Alaska Tango 31,000 Alaska Gamma 24,905 Alaska Sierra 24,190 Would rank like this: Alabama Echo 573,308 1 Alabama Tango 297,498 2 Alabama Delta 101,623 3 Alabama Charlie 93,917 4 Alabama Sierra 93,003 5 Alabama Gamma 64,818 6 Alabama Lima 60,336 7 Arizona Tango 58,392 1 Arizona Romeo 50,037 2 Arizona Charlie 36,424 3 Arizona Lima 32,815 4 Alaska Lima 32,513 1 Alaska Tango 31,000 2 Alaska Gamma 24,905 3 Alaska Sierra 24,190 4 And so on. I can sort the master list with elementary Excel functions, but am stumped on a formula that I can place in an adjacent column and replicate to the end of the rows to do what I want. Thanks in advance for any suggestions. |
multiple ranks in one list
That's likely because some of the cells in Column A contain additional
spaces, special characters, etc. Try cleaning this up and you'll probably find that the formula will return the desired results. I understand that there's a utility available to do this very thing. I believe it's called ASAP. If you'd like to give this a try, you can search for this utility using Google... In article , spndoc wrote: Domenic - Thanks, this is very close, however the value 1 is being replicated. When it ranks it gives a result similar to the following: 1 1 2 3 4 5 1 1 2 3 And so on. Again very close, but not sure how to prevent the number 1 from replicating on the first two lines of each state. |
multiple ranks in one list
On Wed, 3 May 2006 12:39:03 -0700, spndoc
wrote: I am trying to rank a lengthy list of data (about 3500 rows) with a need to rank data as the state name chages so a list that looks like this: Alabama Echo 573,308 Alabama Tango 297,498 Alabama Delta 101,623 Alabama Charlie 93,917 Alabama Sierra 93,003 Alabama Gamma 64,818 Alabama Lima 60,336 Arizona Tango 58,392 Arizona Romeo 50,037 Arizona Charlie 36,424 Arizona Lima 32,815 Alaska Lima 32,513 Alaska Tango 31,000 Alaska Gamma 24,905 Alaska Sierra 24,190 Would rank like this: Alabama Echo 573,308 1 Alabama Tango 297,498 2 Alabama Delta 101,623 3 Alabama Charlie 93,917 4 Alabama Sierra 93,003 5 Alabama Gamma 64,818 6 Alabama Lima 60,336 7 Arizona Tango 58,392 1 Arizona Romeo 50,037 2 Arizona Charlie 36,424 3 Arizona Lima 32,815 4 Alaska Lima 32,513 1 Alaska Tango 31,000 2 Alaska Gamma 24,905 3 Alaska Sierra 24,190 4 And so on. I can sort the master list with elementary Excel functions, but am stumped on a formula that I can place in an adjacent column and replicate to the end of the rows to do what I want. Thanks in advance for any suggestions. Assuming data is in cols A:C, put 1 in D1 and in D2 put =IF(A2=A1,D1+1,1) then copy down col D as far as necessary HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
multiple ranks in one list
Thanks to both Richard and Domenic. I'm nearly to my answer but what I need
to do now is obvious. I have a bit of additional cleaning to do in my source data and I found the answer to that puzzle elsewhere in the discussion lists. Once that cleaning is done your solutions can be applied to my data properly. "Richard Buttrey" wrote: On Wed, 3 May 2006 12:39:03 -0700, spndoc wrote: I am trying to rank a lengthy list of data (about 3500 rows) with a need to rank data as the state name chages so a list that looks like this: Alabama Echo 573,308 Alabama Tango 297,498 Alabama Delta 101,623 Alabama Charlie 93,917 Alabama Sierra 93,003 Alabama Gamma 64,818 Alabama Lima 60,336 Arizona Tango 58,392 Arizona Romeo 50,037 Arizona Charlie 36,424 Arizona Lima 32,815 Alaska Lima 32,513 Alaska Tango 31,000 Alaska Gamma 24,905 Alaska Sierra 24,190 Would rank like this: Alabama Echo 573,308 1 Alabama Tango 297,498 2 Alabama Delta 101,623 3 Alabama Charlie 93,917 4 Alabama Sierra 93,003 5 Alabama Gamma 64,818 6 Alabama Lima 60,336 7 Arizona Tango 58,392 1 Arizona Romeo 50,037 2 Arizona Charlie 36,424 3 Arizona Lima 32,815 4 Alaska Lima 32,513 1 Alaska Tango 31,000 2 Alaska Gamma 24,905 3 Alaska Sierra 24,190 4 And so on. I can sort the master list with elementary Excel functions, but am stumped on a formula that I can place in an adjacent column and replicate to the end of the rows to do what I want. Thanks in advance for any suggestions. Assuming data is in cols A:C, put 1 in D1 and in D2 put =IF(A2=A1,D1+1,1) then copy down col D as far as necessary HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
| All times are GMT +1. The time now is 05:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com