multiple ranks in one list

G

Guest

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.
 
D

Domenic

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!
 
G

Guest

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
 
D

Domenic

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...
 
R

Richard Buttrey

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.
Click to expand...

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
__________________________
 
G

Guest

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.
 
Top