Compare tables & get the missing items

[sharshra]

I have to compare 2 tables & list down the missing data from one of the tables. I tried to use variations of XLOOKUP, XMATCH etc, but not getting the correct output. Can the experts suggest please?

Requirement - Compare the data in the reference table & list the items which are not present in the master table.

Master table:

excel problems.xlsx
	F	G	H
30	name	status	desc
31	nmnm	a
32	opioq	a	78k
33	fd	a	75s
34	vcv	i	098y
35	jkl	i	23f
36	ds	i	666a
37	mmm	a	9m
38	fd	a
39	www	i
40	yty	i
41	wq	a
42	reyu	a	m8
filter

Reference table:

excel problems.xlsx
	B	C
30	name	comment
31	ds	y
32	fd	y
33	reyu
34	jkl	com
35	nmnm	.,.,
36	vcv	n098m,1
37	opioq	,.-`+
38	xzxz	.,m
39	ywqtr	6y
filter

Desired output:

excel problems.xlsx
	B	C
44	name	comment
45	xzxz	.,m
46	ywqtr	6y
filter

 

[Peter_SSs]

Try ..

24 11 16.xlsm
	A	B	C	D	E	F	G	H
30		name	comment			name	status	desc
31		ds	y			nmnm	a
32		fd	y			opioq	a	78k
33		reyu				fd	a	75s
34		jkl	com			vcv	i	098y
35		nmnm	.,.,			jkl	i	23f
36		vcv	n098m,1			ds	i	666a
37		opioq	,.-`+			mmm	a	9m
38		xzxz	.,m			fd	a
39		ywqtr	6y			www	i
40						yty	i
41						wq	a
42						reyu	a	m8
43
44		name	comment
45		xzxz	.,m
46		ywqtr	6y
47
Sheet7

Cell Formulas
Range	Formula
B45:C46	B45	=FILTER(B31:C39,ISNA(MATCH(B31:B39,F31:F42,0)),"")
Dynamic array formulas.

 

[HighAndWilder]

I have to compare 2 tables & list down the missing data from one of the tables. I tried to use variations of XLOOKUP, XMATCH etc, but not getting the correct output. Can the experts suggest please?

Requirement - Compare the data in the reference table & list the items which are not present in the master table.

Master table:

excel problems.xlsx
	F	G	H
30	name	status	desc
31	nmnm	a
32	opioq	a	78k
33	fd	a	75s
34	vcv	i	098y
35	jkl	i	23f
36	ds	i	666a
37	mmm	a	9m
38	fd	a
39	www	i
40	yty	i
41	wq	a
42	reyu	a	m8
filter

Reference table:

excel problems.xlsx
	B	C
30	name	comment
31	ds	y
32	fd	y
33	reyu
34	jkl	com
35	nmnm	.,.,
36	vcv	n098m,1
37	opioq	,.-`+
38	xzxz	.,m
39	ywqtr	6y
filter

Desired output:

excel problems.xlsx
	B	C
44	name	comment
45	xzxz	.,m
46	ywqtr	6y
filter

If they are actually tables that you are using.

Compare tables & get the missing items.xlsm
	A	B
1	name	comment
2	xzxz	.,m
3	ywqtr	6y
Output

Cell Formulas
Range	Formula
A1:B3	A1	=VSTACK({"name","comment"},FILTER(Reference,ISNA(MATCH(Reference[name],Master[name],0)),""))
Dynamic array formulas.

 

[sharshra]

Thanks, @Peter_SSs. It works on the sample tables

But when I apply it on the actual data, MATCH is not returning the correct values. Though there are no duplicate names, I see that MATCH is returning duplicate & incorrect return values. Not sure why. Any suggestions? Unfortunately, I can´t share the actual data since it is confidential.

 

[Peter_SSs]

I can´t share the actual data since it is confidential.

Then construct some dummy data (or just disguise the original) that displays the problem?

If the suggested formula works with the sample data provided then it seems there is nothing fundamentally wrong with the formula.
More likely something unusual about the actual data or how the formula was applied in your worksheet. For that we can't really guess.

 

[sharshra]

@Peter_SSs, I figured it out! In the MATCH, I missed 0 (exact match) & that was causing the issue. Now it is fixed Thanks a lot for your help

 

[Peter_SSs]

Glad you got it sorted. Thanks for letting us know.