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

Excel 365 Pro Plus with Power Query.

With latest OP data.

https://www.mediafire.com/file/sk3gy0txg764x1p/02_25_22.xlsx/file

https://www.mediafire.com/file/2nzs0tdeneflfvw/02_25_22.pdf/file

Answer 3

Oops - change the formula to

=IF(ISERROR(MATCH($A2;$F:$F;FALSE));"";IF(COUNTIFS($F:$F;$A2;G:G;B2)=0;B2 & " - " & VLOOKUP($A2;$F:$I;COLUMN(B2);False);B2))

Answer 4

Hi,

Refer to the Merge1 worksheet. You may download the solution workbook from here.

Hope this helps.