Compare two files with multiple criteria to get a matching reconciliation

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
242
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,714
Members
414,013
Latest member
tnobbs

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