Matching check to payment amounts

  • Thread starter Thread starter Donna
  • Start date Start date
D

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
 
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.
 
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.
 
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).
 
Yes there are blank lines
Ck# TRN
25391 #DIV/0! R
25391 R

25572 R

1387 R
ach081209

26513 R

26596 R
 
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.
 
Back
Top