Matching check to payment amounts

[Donna]

If on my spreadsheet I break out a column that just has checks in (col A) I
would like to in column (B) match all the places that the check is applied. I
have used numbers in col (B) but if I could use the letters of the alphabet
that would even be better, but either way would be a great help.
A B c d

1235 1 R $500.00
1795 2 R $150.00
1999 3 R $ 12.00
1795 2 R $545.00
2007 4 R $195.00
1235 1 R $100.00
1795 2 R $ 10.00
Thanks Donna

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[T. Valko]

I'm not sure how you'd get consecutive letters. What happens if you have
more than 26 unique check numbers?

This will give your number sequence:

=SUMPRODUCT(--(A2>A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

Basically, it gives a "rank" to the check number.

 

[T. Valko]

I'm not sure how you'd get consecutive letters.

Well, I didn't see the solution that was already in front of me!

=CHAR(64+SUMPRODUCT(--(A2>A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1)

However, that won't work too well if you have more than 26 unique check
numbers (letters in the alphabet).

 

[Donna]

Hi,
I must be doing something wrong, I get #Div/0!
Thanks
Donna

 

[T. Valko]

I must be doing something wrong, I get #Div/0!

Are there any empty cells in the range?

 

[Donna]

Yes there are blank lines
Ck# TRN
25391 #DIV/0! R
25391 R

25572 R

1387 R
ach081209

26513 R

26596 R

 

[T. Valko]

Try one of these array formulas**...

For the numeric ranks:

=IF(A2<>"",SUM(IF($A$2:$A$8<>"",IF(A2>$A$2:$A$8,1/COUNTIF($A$2:$A$8,$A$2:$A$8))))+1,"")

For the letter ranks:

=IF(A2<>"",CHAR(64+SUM(IF($A$2:$A$8<>"",IF(A2>$A$2:$A$8,1/COUNTIF($A$2:$A$8,$A$2:$A$8))))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.