+ Reply to Thread
Results 1 to 6 of 6

Many to one matching from 2 columns (bank reconciliation)

  1. #1
    Registered User
    Join Date
    03-04-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    7

    Many to one matching from 2 columns (bank reconciliation)

    Hi,

    i want to create a file that helps me, in a monthly basis, to do the bank reconciliation.
    I have manage to use some formulas to match 1 to 1 the values between accounting and the bank statement, as well the duplicated values.

    However, i need to match many data to one matching, because we can pay to say 50 different suppliers at once, but in bank side it will only show one line.
    What i need is to run a macro that only use the data where is no "x" or "duplicated values" and try to reconcile the movements and then place an x next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

    I attached a sample of the file. This file have all data manual reconciled, but i need to add a code on button1 to do the last step.

    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Many to one matching from 2 columns (bank reconciliation)

    I know what you trying to do, but I doubt if you could achieve a full bank rec using a code. If it were easy, we would not have seen many software selllers developing a bank rec application. Yes, excel and VBA could reduce the manual rec, but you can not 100 rely on it.
    The hardes bit is getting a combination of duplicates. For e.g you have 20 entries in a bank for one single transaction in your cash book, so you have to write a code which finds hunderds combination in order to get a single sum figure. This will be comounded by duplicate amount.
    Yes, you can easily do a one to one and duplicate match in excel, but that is not what goes in the real world of banking and finance. This is just my opinion and hopefully others may have different perspective.

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    India
    MS-Off Ver
    Excell 2010
    Posts
    22

    Re: Many to one matching from 2 columns (bank reconciliation)

    You can try using solver add in to find Many to one match.

    Search this forum for Solver, you will get an idea.

    Quote Originally Posted by JoaoFerreira1985 View Post
    Hi,

    i want to create a file that helps me, in a monthly basis, to do the bank reconciliation.
    I have manage to use some formulas to match 1 to 1 the values between accounting and the bank statement, as well the duplicated values.

    However, i need to match many data to one matching, because we can pay to say 50 different suppliers at once, but in bank side it will only show one line.
    What i need is to run a macro that only use the data where is no "x" or "duplicated values" and try to reconcile the movements and then place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

    I attached a sample of the file. This file have all data manual reconciled, but i need to add a code on button1 to do the last step.

    Thanks in advance

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Many to one matching from 2 columns (bank reconciliation)

    Mano,
    You are right!
    I have also seen it in other forums, but have not came across it my self. If you have, please share with us your suggestion.

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    India
    MS-Off Ver
    Excell 2010
    Posts
    22

    Re: Many to one matching from 2 columns (bank reconciliation)

    Hi Ab33,

    Well i have started using solver recently, its will working on many to one matching,

    just have a look on the below link to know about solver and how to enable that.

    http://....excel-find-a-subset-of-numbers-that-add-to-a-given-total

    Quote Originally Posted by AB33 View Post
    Mano,
    You are right!
    I have also seen it in other forums, but have not came across it my self. If you have, please share with us your suggestion.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Many to one matching from 2 columns (bank reconciliation)

    Thanks!
    I will have a look

+ Reply to 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