Compare two files with multiple criteria to get a matching reconciliation

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
243
HI Guys / Girls
I have what I thought would be a simple task, but for me it is not.
In column A & B I have my Amex CC data, date transactions.
In column C & D I have my Amex CC data from my account software, date transactions.
Without out boring you the EU introduced some new security parameters and bank feeds to account software.
But the new feed is currently missing a few items and combined with Xero using Hubdoc (automated doc reader) to automate entries I keep getting bad or no data.
I have tried to run a compare two lists which is normally easy but not in this case.
Take it A&B is correct I need to know how to compare C&D with A&B to find what is missing.
The issue becomes more difficult as the dates in C column might be incorrect even if the correct amount is present.
I need to know 2 things
1. The incorrect dates in column C which are already in both lists.
2. The missing transaction values and dates. I think there is only 3 missing entries
To assist
Amex Bal £3192
Xero Amex Bal £2862.14
Diff £329.86
I have tried several ways but not found a way to achieve it. I would love to do in in PQ so I can just drop in two files and refresh until they fix the issue.


File Reconciliation Query File .xlsx
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,545
Messages
5,832,387
Members
430,129
Latest member
EmilyCrandall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top