If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

USD $60.00 Reconciliate two worksheet and display differences

6$ paid to ozgrid (transaction ID 9MV140662P067244U)

I would need a macro in the Reco.xlsx file that would open the workbooks Invoices.xlsx

Then, the macro should:
- Filter column A of the invoices to take only "Invoice" entries
- Match the column Account from Payments worksheet with the column Account from Invoices.xlsx
- Match the column SubAccount from Payments worksheet with the column SubAccount from Invoices.xlsx (the format is slightly different: the account starts with xxx in the Invoices spreadsheet )
- Compare the column Amount from Payments worksheet with the column Amount from Invoices.xlsx (a tolerance of 10% should be accepted, i.e 6,4 on the one hand and 6,7 on the other hand should be considered as matched)

=> Matched items should be displayed side by side on the reco.xlsx spreadsheet as shown
- Any entry in one sheet that cannot be matched on the other sheet should appear in reco.xlsx as unmatched

Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

Re: USD $60.00 Reconciliate two worksheet and display differences

Re: USD $60.00 Reconciliate two worksheet and display differences

Hello

I finished the code. I will send you a PM for the PayPal payment and upon receiving the funds I will upload my code.
It's almost 2 AM here in Belgium so depending on how fast the payment is done, I could upload the solution tomorrow morning.

Re: USD $60.00 Reconciliate two worksheet and display differences

Hi,

Thanks, funds received. Attached my coding. Can you give a try please ?
2 remarks:
- press Alt-F8 to go to the macro's. The workbook contains only 1 macro.
- I removed the first empty row in the invoices sheet, and also some other empty rows/columns. This makes the code easier.
Please also make sure the first empty row in the Invoices sheet is deleted before running the macro.

Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

Re: USD $60.00 Reconciliate two worksheet and display differences

Hello

Many thanks, it looks very good!
I have some minor remarks:
- I would like to be able to keep some rows on the top (I cannot really change input files)
- the macro does not seem to reconcile the line 979.4€ from Invoices with the 970€ amount from the Payments sheet, although amounts are well within the 10% tolerance

Re: USD $60.00 Reconciliate two worksheet and display differences

Hello,

Okay, your call. But then range("A2") (which is cell A2) on the Invoices sheet is hardcoded in the VBA-code.
I changed that one.
Also, that 970 is matched now (probably it did not match if you have a blank row and did not change for Range("A2").

Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.