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

Use this in L2

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

And use this in M2, copied to N2 and O2

=IF(ISERROR(MATCH($A2;$F:$F;FALSE));"";IF(COUNTIFS($F:$F;$A2;G:G;B2)=0;B2 & " - " & G2;B2))

Then copy L2:O2 down to match entries in A

Answer 3

Hi,

Using the Query Editor, one can merge 2 tables. To get more specific help, share data in a format that can be pasted in an MS Excel file.

Answer 4

The "MISSING"/"CHANGED" formula works well !

However, there is an issue with the second formula.

In it, you assume that the "CHANGED" values (in this instance B2 and G2) are in the same row, but in actuality it can be in a different row of the range.

So even in your example, it compares values of V01-B12-HU01 (B2) and V01-B12-HU06 (G2), when it's suppose to be of the same type (...-HU01 and ...-HU01).

Answer 5

Hey, I couldn't find an upload file function so I just pasted the tables here.

Table 1

Littra	DIA	WIDTH	HEIGHT
V01-B12-HU01	680
V01-B12-HU02		1250	1450
V01-B12-HU03		1390	1190
V01-B12-HU04	370
V01-B12-HU05		1050	1050
V01-B12-HU06		1650	1050
V01-B12-HU07		1650	1050
V01-B12-HU08		650	650
V01-B12-HU09	680
V01-B12-HU10	450
V01-B12-HU11		1050	1050
V01-B12-HU12	250
V01-B12-HU13		1650	1050
V01-B12-HU14		1650	1050
V01-B12-HU15		3050	1250
V01-B12-HU500		684	202
V01-B12-HU501	180
V01-B12-HU502		510	230
V01-B12-HU504	131
V01-B12-HU505	120
V01-B12-HU506	374
V01-B12-HU507	374
V01-B12-HU508	374
V01-B12-HU509	374
V01-B12-HU510	228
V01-B12-HU511	228
V01-B12-HU512	374
V01-B12-HU513	374

Table 2

Littra	DIA	WIDTH	HEIGHT
V01-B12-HU06		1650	1050
V01-B12-HU07		1650	1050
V01-B12-HU08		650	650
V01-B12-HU09	450
V01-B12-HU10	370
V01-B12-HU11		1050	1050
V01-B12-HU12	250
V01-B12-HU13		1850	1050
V01-B12-HU14		1850	1050
V01-B12-HU15		3050	1250
V01-B12-HU500		684	202
V01-B12-HU502		510	230
V01-B12-HU504	131
V01-B12-HU505	120
V01-B12-HU506	374
V01-B12-HU507	374
V01-B12-HU508	374
V01-B12-HU509	374
V01-B12-HU510	228
V01-B12-HU511	228
V01-B12-HU512	374
V01-B12-HU513	374