1

How can I match credit card batch payments from bank statement to credit card purchases in a spreadsheet? 

CC       Purchases   Batch  Payments    
1        0.05        A      1,913.91    
2        10.23       B      7,812.45    
3        12.00              
4        22.11              
5        23.25              
6        27.36              
7        50.66              
8        66.99              
9        100.01                 
10       100.99                 
11       300.10                 
12       400.00                 
13       789.23                 
14       900.69                 
15       907.07                 
16       2,015.33               
17       4,000.29               

Total    9,726.36
Improve this question
  • I edited your formatting to make the table much easier to read. However, I still don't quite understand what you are looking for. Can you explain how the formula should work to go from Purchases to Payments? – Michael Frank Apr 28 '15 at 20:36
  • Hi Michael. I need to match what purchases 1-17 make up batch payments A and B. – Bryan Apr 28 '15 at 21:07
  • 1
    Do you want a formula to decide which of the 17 purchases add up to $1,913.91 and which add up to $7,812.45? – Clif Apr 29 '15 at 2:30
  • Given that there are only two batch payment made,this could be simple to solve. But what if the number of purchases and batch payment are more? It is likely that one purchase might fit equally in both payment. – Prasanna Apr 29 '15 at 3:08
  • Lets assume everything is in chronological order, you pay the balance in full each month, there are no exceptions (interest, disputed amounts, etc.), and your first purchase entry is also the first that applies to the first payment entry. You could just add successive purchases until the cumulative total matches a payment, then start over. If there are any exceptions, it becomes trial and error looping, like CdeoEm's answer or a VBA solution. – fixer1234 Apr 29 '15 at 4:09
0

If my understanding of the problem is correct and you are looking for a singular solution to match each batch payment it is possible to work out using the solver add-in which is available through file->options->Add-Ins->Solver-Addin.

The details on how to use this add-in are outlined very well in this previous post: Excel: Find a subset of numbers that add to a given total?

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.