Matching check to payment amounts
 

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

Matching check to payment amounts
 

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Donna" wrote in message
...
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

Matching check to payment amounts
 

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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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

Matching check to payment amounts
 

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(--(A2A$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).

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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

Matching check to payment amounts
 

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

"T. Valko" wrote:

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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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

Matching check to payment amounts
 

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

Are there any empty cells in the range?

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
Hi,
I must be doing something wrong, I get #Div/0!
Thanks
Donna

"T. Valko" wrote:

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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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

Matching check to payment amounts
 

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

25572 R

1387 R
ach081209

26513 R

26596 R



"T. Valko" wrote:

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

Are there any empty cells in the range?

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
Hi,
I must be doing something wrong, I get #Div/0!
Thanks
Donna

"T. Valko" wrote:

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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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

Matching check to payment amounts
 

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.

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
Yes there are blank lines
Ck# TRN
25391 #DIV/0! R
25391 R

25572 R

1387 R
ach081209

26513 R

26596 R



"T. Valko" wrote:

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

Are there any empty cells in the range?

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
Hi,
I must be doing something wrong, I get #Div/0!
Thanks
Donna

"T. Valko" wrote:

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(--(A2A$2:A$8),1/COUNTIF(A$2:A$8,A$2:A$8))+1

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

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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