Cross-reference data check

Question

Hello,

I'm trying to cross-reference check the data from two separate tables to see what is missing and which data does not match.

Currently, I managed to check the differences between two File columns using =IF(ISERROR(MATCH(A2;$F$2:$F$31;0));"MISSING: "&A2;A2) formula, however I am having problems with the data that goes along with the item in question. The formula used above finds values from the range that matches the origin, but in this case I need it to take the data from specific files, e.i. use and check V01-B12-HU06 data from both lists.

Any help in solving this is appreciated.

Answer 1

What I like to do is make a copy of the first table, with an extra column filled with and identifier: "First Table" works well. Then I copy the second table beneath the first table, and fill the extra column with "Second Table".

Then the magic: use Data / Remove duplicates, unchecking the extra column. Then all the entries in the Second Table section that are unchanged will be removed, and only the changed data will remain (in the rows marked "Second Table")

Then do the reverse - second table on top - and the rows that remain in the "First Table" section were either changed or removed from the Second Table.

OR - use a formula like

=IF(COUNTIFS(F:F;A2;G:G;B2;H:H;C2;I:I;D2)=0;"CHANGED or MISSING: "&A2 &" "& B2 &" "& C2 &" "& D2;A2 &" "& B2 &" "& C2 &" "& D2)

Answer 2

Hi,

Your question is not clear. Please show the expected result very clearly.

Answer 3

Excel 365 Pro Plus with Power Query.

Check for missing and mismatch.

No formulas, no VBA macro.

https://www.mediafire.com/file/zh7tk709qxrnopm/02_23_22a.xlsx/file

https://www.mediafire.com/file/le3i42e53dhvq1p/02_23_22a.pdf/file

Answer 4

Thanks a lot, Bernie, the formula did the job for me for the most part.
For a "complete" solution I'd still like to see both values which are different (for visual comparison) from both tables when they are reorganized with the formula, For example.:

T.R.

Answer 5

Sorry if my expected results weren't clear, to put it simply:

1. Check which row is missing in the second table compared to the first;
2. Check if the data provided in the same row of both tables are the same;
3. Output the results in another table to visually see the results.

As shown in picture:

Best,
T.R.