# Looking for an excel formula to match credit card purchases to batch payments reflected on bank statement for account reconciliation [closed]

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
``````
• 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
• 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