Identifying Items NOT in two separate lists

[Zakky]

Hello, All.
Is there a way to identify items that do not appear in two separate lists. I reconcile two lists to pick out items that don't appear on both lists. The data can be in various forms i.e. text, number or both. The items must be exact match and upper/lower case is not important. I have upload an image for illustration. Thank you.

 

[Rick Rothstein]

Give this formula a try...

=UNIQUE(TOCOL(A2:B12),,1)

 

[Zakky]

Rick Rothstein, thank you for the prompt reply. works like a dream...super helpful.

 

[keda duck]

Rick Rothstein's formula is incorrect. This formula is a way to identify items that do not appear in two separate lists.

text.xlsx
	A	B	C	D	E
1	list 1	list 2		result	Ignore order
2	aA	5		x	1E
3	5	1E		1d	x
4	5g	2b		1E	1d
5	7h	5g		z	z
6	h6	67b
7	y7	7h
8	thy7	AA
9	67b	h6
10	2b	thy7
11	x	y7
12	1d	z
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=TOCOL(IF(COUNTIF(OFFSET(A2:A12,,{1,0}),A2:B12),\,A2:B12),3,1)
E2:E5	E2	=TOCOL(IF(COUNTIF(OFFSET(A2:A12,,{1,0}),A2:B12),\,A2:B12),3)
Dynamic array formulas.

 

[johnnyL]

Rick Rothstein's formula is incorrect. This formula is a way to identify items that do not appear in two separate lists.

text.xlsx
	A	B	C	D	E
1	list 1	list 2		result	Ignore order
2	aA	5		x	1E
3	5	1E		1d	x
4	5g	2b		1E	1d
5	7h	5g		z	z
6	h6	67b
7	y7	7h
8	thy7	AA
9	67b	h6
10	2b	thy7
11	x	y7
12	1d	z
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=TOCOL(IF(COUNTIF(OFFSET(A2:A12,,{1,0}),A2:B12),\,A2:B12),3,1)
E2:E5	E2	=TOCOL(IF(COUNTIF(OFFSET(A2:A12,,{1,0}),A2:B12),\,A2:B12),3)
Dynamic array formulas.

Not sure how you figure that @Rick Rothstein formula is incorrect. If you compare the same data, the same results are returned.

 

[Zakky]

Keda duck, thank you for the prompt reply. It's nice to have it sorted in order as presentation is important. The formula in 'Ignore order' gives the same result as Rick Rothstein'.

 

[keda duck]

Not sure how you figure that @Rick Rothstein formula is incorrect. If you compare the same data, the same results are returned.

sorry, I forgot the meaning of the third argument

 

[Rick Rothstein]

Keda duck, thank you for the prompt reply. It's nice to have it sorted in order as presentation is important. The formula in 'Ignore order' gives the same result as Rick Rothstein'.

You never said the sort order was important before now. Given that it is, then use this version of my formula...

=UNIQUE(TOCOL(A2:B12,,1),,1)

 

[Zakky]

Rick Rothstein, now it's perfect....many thanks for fine-tuning it. have a good day.