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
__________________________