| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to find matching criteria in 1 column, then add amounts in a | Excel Worksheet Functions | |||
| How do I use Excel 2007 to determine late payment amounts | Excel Discussion (Misc queries) | |||
| Is there a template for a Check Payment Voucher | Excel Worksheet Functions | |||
| Format dollar amounts for check printing | Excel Discussion (Misc queries) | |||
| matching dupliclates and then looking up the corresponding amounts | Excel Discussion (Misc queries) | |||
![Donna[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
