software

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 Delta101,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 Charlie36,424
Arizona Lima 32,815
Alaska Lima 32,513
Alaska Tango31,000
Alaska Gamma 24,905
Alaska Sierra 24,190

Would rank like this:

Alabama Echo 573,308 1
Alabama Tango 297,498 2
Alabama Delta101,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 Charlie36,424 3
Arizona Lima 32,815 4
Alaska Lima 32,513 1
Alaska Tango31,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 that A1:C15 contains the data, try...

D1, copied down:

=SUMPRODUCT(--($A$1:$A$15=A1),--(C1lt;$C$1:$C$15)) 1

Hope this helps!

In article gt;,
spndoc gt; wrote:

gt; I am trying to rank a lengthy list of data (about 3500 rows) with a need to
gt; rank data as the state name chages so a list that looks like this:
gt;
gt; Alabama Echo 573,308
gt; Alabama Tango 297,498
gt; Alabama Delta101,623
gt; Alabama Charlie 93,917
gt; Alabama Sierra 93,003
gt; Alabama Gamma 64,818
gt; Alabama Lima 60,336
gt; Arizona Tango 58,392
gt; Arizona Romeo 50,037
gt; Arizona Charlie36,424
gt; Arizona Lima 32,815
gt; Alaska Lima 32,513
gt; Alaska Tango31,000
gt; Alaska Gamma 24,905
gt; Alaska Sierra 24,190
gt;
gt; Would rank like this:
gt;
gt; Alabama Echo 573,308 1
gt; Alabama Tango 297,498 2
gt; Alabama Delta101,623 3
gt; Alabama Charlie 93,917 4
gt; Alabama Sierra 93,003 5
gt; Alabama Gamma 64,818 6
gt; Alabama Lima 60,336 7
gt; Arizona Tango 58,392 1
gt; Arizona Romeo 50,037 2
gt; Arizona Charlie36,424 3
gt; Arizona Lima 32,815 4
gt; Alaska Lima 32,513 1
gt; Alaska Tango31,000 2
gt; Alaska Gamma 24,905 3
gt; Alaska Sierra 24,190 4
gt;
gt; And so on. I can sort the master list with elementary Excel functions, but
gt; am stumped on a formula that I can place in an adjacent column and replicate
gt; to the end of the rows to do what I want.
gt;
gt; Thanks in advance for any suggestions.

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.quot;Domenicquot; wrote:

gt; Assuming that A1:C15 contains the data, try...
gt;
gt; D1, copied down:
gt;
gt; =SUMPRODUCT(--($A$1:$A$15=A1),--(C1lt;$C$1:$C$15)) 1
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; spndoc gt; wrote:
gt;
gt; gt; I am trying to rank a lengthy list of data (about 3500 rows) with a need to
gt; gt; rank data as the state name chages so a list that looks like this:
gt; gt;
gt; gt; Alabama Echo 573,308
gt; gt; Alabama Tango 297,498
gt; gt; Alabama Delta101,623
gt; gt; Alabama Charlie 93,917
gt; gt; Alabama Sierra 93,003
gt; gt; Alabama Gamma 64,818
gt; gt; Alabama Lima 60,336
gt; gt; Arizona Tango 58,392
gt; gt; Arizona Romeo 50,037
gt; gt; Arizona Charlie36,424
gt; gt; Arizona Lima 32,815
gt; gt; Alaska Lima 32,513
gt; gt; Alaska Tango31,000
gt; gt; Alaska Gamma 24,905
gt; gt; Alaska Sierra 24,190
gt; gt;
gt; gt; Would rank like this:
gt; gt;
gt; gt; Alabama Echo 573,308 1
gt; gt; Alabama Tango 297,498 2
gt; gt; Alabama Delta101,623 3
gt; gt; Alabama Charlie 93,917 4
gt; gt; Alabama Sierra 93,003 5
gt; gt; Alabama Gamma 64,818 6
gt; gt; Alabama Lima 60,336 7
gt; gt; Arizona Tango 58,392 1
gt; gt; Arizona Romeo 50,037 2
gt; gt; Arizona Charlie36,424 3
gt; gt; Arizona Lima 32,815 4
gt; gt; Alaska Lima 32,513 1
gt; gt; Alaska Tango31,000 2
gt; gt; Alaska Gamma 24,905 3
gt; gt; Alaska Sierra 24,190 4
gt; gt;
gt; gt; And so on. I can sort the master list with elementary Excel functions, but
gt; gt; am stumped on a formula that I can place in an adjacent column and replicate
gt; gt; to the end of the rows to do what I want.
gt; gt;
gt; gt; Thanks in advance for any suggestions.
gt;

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 gt;,
spndoc gt; wrote:

gt; Domenic - Thanks, this is very close, however the value 1 is being
gt; replicated. When it ranks it gives a result similar to the following:
gt;
gt; 1
gt; 1
gt; 2
gt; 3
gt; 4
gt; 5
gt; 1
gt; 1
gt; 2
gt; 3
gt;
gt; And so on.
gt;
gt; Again very close, but not sure how to prevent the number 1 from replicating
gt; on the first two lines of each state.

On Wed, 3 May 2006 12:39:03 -0700, spndoc
gt; wrote:

gt;I am trying to rank a lengthy list of data (about 3500 rows) with a need to
gt;rank data as the state name chages so a list that looks like this:
gt;
gt;Alabama Echo 573,308
gt;Alabama Tango 297,498
gt;Alabama Delta101,623
gt;Alabama Charlie 93,917
gt;Alabama Sierra 93,003
gt;Alabama Gamma 64,818
gt;Alabama Lima 60,336
gt;Arizona Tango 58,392
gt;Arizona Romeo 50,037
gt;Arizona Charlie36,424
gt;Arizona Lima 32,815
gt;Alaska Lima 32,513
gt;Alaska Tango31,000
gt;Alaska Gamma 24,905
gt;Alaska Sierra 24,190
gt;
gt;Would rank like this:
gt;
gt;Alabama Echo 573,308 1
gt;Alabama Tango 297,498 2
gt;Alabama Delta101,623 3
gt;Alabama Charlie 93,917 4
gt;Alabama Sierra 93,003 5
gt;Alabama Gamma 64,818 6
gt;Alabama Lima 60,336 7
gt;Arizona Tango 58,392 1
gt;Arizona Romeo 50,037 2
gt;Arizona Charlie36,424 3
gt;Arizona Lima 32,815 4
gt;Alaska Lima 32,513 1
gt;Alaska Tango31,000 2
gt;Alaska Gamma 24,905 3
gt;Alaska Sierra 24,190 4
gt;
gt;And so on. I can sort the master list with elementary Excel functions, but
gt;am stumped on a formula that I can place in an adjacent column and replicate
gt;to the end of the rows to do what I want.
gt;
gt;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
__________________________

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.

quot;Richard Buttreyquot; wrote:

gt; On Wed, 3 May 2006 12:39:03 -0700, spndoc
gt; gt; wrote:
gt;
gt; gt;I am trying to rank a lengthy list of data (about 3500 rows) with a need to
gt; gt;rank data as the state name chages so a list that looks like this:
gt; gt;
gt; gt;Alabama Echo 573,308
gt; gt;Alabama Tango 297,498
gt; gt;Alabama Delta101,623
gt; gt;Alabama Charlie 93,917
gt; gt;Alabama Sierra 93,003
gt; gt;Alabama Gamma 64,818
gt; gt;Alabama Lima 60,336
gt; gt;Arizona Tango 58,392
gt; gt;Arizona Romeo 50,037
gt; gt;Arizona Charlie36,424
gt; gt;Arizona Lima 32,815
gt; gt;Alaska Lima 32,513
gt; gt;Alaska Tango31,000
gt; gt;Alaska Gamma 24,905
gt; gt;Alaska Sierra 24,190
gt; gt;
gt; gt;Would rank like this:
gt; gt;
gt; gt;Alabama Echo 573,308 1
gt; gt;Alabama Tango 297,498 2
gt; gt;Alabama Delta101,623 3
gt; gt;Alabama Charlie 93,917 4
gt; gt;Alabama Sierra 93,003 5
gt; gt;Alabama Gamma 64,818 6
gt; gt;Alabama Lima 60,336 7
gt; gt;Arizona Tango 58,392 1
gt; gt;Arizona Romeo 50,037 2
gt; gt;Arizona Charlie36,424 3
gt; gt;Arizona Lima 32,815 4
gt; gt;Alaska Lima 32,513 1
gt; gt;Alaska Tango31,000 2
gt; gt;Alaska Gamma 24,905 3
gt; gt;Alaska Sierra 24,190 4
gt; gt;
gt; gt;And so on. I can sort the master list with elementary Excel functions, but
gt; gt;am stumped on a formula that I can place in an adjacent column and replicate
gt; gt;to the end of the rows to do what I want.
gt; gt;
gt; gt;Thanks in advance for any suggestions.
gt;
gt; Assuming data is in cols A:C,
gt; put 1 in D1 and
gt; in D2 put
gt;
gt; =IF(A2=A1,D1 1,1)
gt;
gt; then copy down col D as far as necessary
gt;
gt; HTH
gt;
gt;
gt; __
gt; Richard Buttrey
gt; Grappenhall, Cheshire, UK
gt; __________________________
gt;