Many to one matching from 2 columns (bank reconciliation)

[JoaoFerreira1985]

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

[AB33]

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.

[manoj.0790]

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

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

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

[AB33]

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.

[manoj.0790]

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

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.

[AB33]

Thanks!
I will have a look