Closed Thread
Results 1 to 13 of 13

Bank reconciliation - Many to one matching and partial cell matching

  1. #1
    Registered User
    Join Date
    11-19-2009
    Location
    NL
    MS-Off Ver
    Excel 2003
    Posts
    3

    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

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Sample copies of your workbook would be helpful to see if I can create links among the data.
    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    11-19-2009
    Location
    NL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Bank reconciliation - Many to one matching and partal cell matching

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Hi M
    This code (Adapted from http://www.sqldrill.com/excel/genera...um-amount.html) is in the attached workbook
    Please Login or Register  to view this content.
    On your sample data, it seems to address this issue
    Many to one matching
    Your sample file contains no examples of the second issue
    Partial cell matching
    so I'm not able to experiment with an approach to accomplish the second issue. However, using the date as part of the match may be difficult as the Bank date and the Book date will seldom be the same.
    Having said that, I'll be glad to look at it if you can give me some data to look at. If you so choose, please include data that represents both issues as they may well overlap.
    If you have problems with the attached, let me know.
    John
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-19-2009
    Location
    NL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Bank reconciliation - Many to one matching and partal cell matching

    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.
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Hello maartendelaet,

    You could make this much easier by adding unique customer numbers. In American accounting systems, it is a standard practice to use either a transaction number or customer number or both. This greatly simplifies tracking trough various journals.

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Hi M
    I'll take a look at it.
    John

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Hi M
    I've got this pretty much surrounded but the procedure is flagging one record for no apparent reason. I'll keep looking at it but I thought, since you're familiar with the data, you might look at it and say "Ah ha, I know why it's doing that". The file is attached.
    John
    Attached Files Attached Files

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Hi M
    I haven't figured out why that extraneous record (10.07) flags as reconciled but I may have found a work around. I've added a procedure that reconciles what appear to be book entries. These entries are cleared and it "fixes" the problem I was having with the extraneous record. Try this amended procedure on a live copy of a previous reconciliation and see if you get the same results.
    In my career, I've done thousands of Bank Recs so I'm aware you need to deal with information that's available. The attached procedures may or may not deal with available information in the proper manner. Try it and let me know where the issues are. If we can fix it we will. If not, some "grunt" work may be left to do.
    John
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-22-2010
    Location
    Santa Barbara, CA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Bank reconciliation - Many to one matching and partal cell matching

    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
    Attached Files Attached Files

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Bank reconciliation - Many to one matching and partal cell matching

    Welcome to the Board, however, please note:

    Your post does not comply with Rule 2 of our Forum RULES.
    Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  12. #12
    Registered User
    Join Date
    01-22-2010
    Location
    Santa Barbara, CA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Bank reconciliation - Many to one matching and partial cell matching

    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

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Bank reconciliation - Many to one matching and partial cell matching

    James, please reread DonkeyOte's post.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1