Bank reconciliation - Many to one matching and partial cell matching

Hi everyone,

I am hoping to get some help with programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

I hope the above descriptions of what I am looking for are clear. Any suggestions on how to accomplish this are more than welcome. Looking forward to your input.

Thanks,
Maarten

Hi!

Thanks for wanting to take a look at this.

I attached the file which would give a basic overview of the data format. Normally either side might still have another column with the supplier name as described in my earlier post.

Hope this gives you enough to work with.

Thanks so much in advance!!

M.

Hi John,

First of all thank you so much for getting back to me so quickly with a code that seems to be working brilliantly with the previous file.

Unfortunately I had simplified the data for privacy reasons, but I might have made the file a bit too simple. Running the macro on another file that has additional data it stops working perfectly.

I attached another file with additional lines. If you run the macro, you'll see that it doesn't match exactly anymore.

When I originally thought about how to tackle this issue, I thought there might be some code that looks at the totals of a combination of date and line description. These totals should match with an entry on the bank side.

Thanks again for helping me so far. Looking forward to hearing back from you.

Regards,
M.

This has been very helpful. ! I didn't even know it was possible to use Excel in this fashion but after some programming refreshing I hope to create a Macro that will do bank reconciliations that aren't quite as complicated as your example. My bank recs are mostly even matched dollar amounts but scattered throughout the excel sheet. (See example)

Have you seen or know of a macro for matching the excel sheet I have? This would save me about 2 weeks of work per month.

Thanks,
James

Hello!
I might not be doing this right but I can't get the 'One to Many matching' of this macro to work. In other words, if there are two $50 deposits on the left and but only one $100 deposit on the right, is it supposed to match? Or should I start a new string?

I really appreciate this site.
-James