How to find what number in Column A is not included in Column B?

Z

Zhi Sheng

example:
A B
1 1
2 3
3 4
4 6
5 7
8
Here "2" and "5" are in A but not in B. Is there an easy way to highlight
numbers like this for two long lists of data?

Thank you
 
P

Pete_UK

Highlight cells A1:A5, with A1 as the active cell, and click on Format
| Conditional Formatting. In the dialogue box choose Formula Is rather
than Cell Value Is, and enter this formula:

=ISNA(MATCH(A1,B$1:B$6,0))

then click on the Format button, choose the Patterns tab (for
background colour) and choose red. Click OK twice to exit the dialogue
box, and the numbers 2 and 5 should be highlighted.

Then select B1:B6, and click on Format | Conditional Formatting again.
This time use this formula:

=ISNA(MATCH(B1,A$1:A$5,0))

click on the Format button and choose Blue for the background colour,
then OK your way out - the numbers 6, 7 and 8 should be highlighted in
blue.

Hope this helps.

Pete
 
Z

Zhi Sheng

Excellent, thanks Pete !
--
====================
ask and you shall be answered


Pete_UK said:
Highlight cells A1:A5, with A1 as the active cell, and click on Format
| Conditional Formatting. In the dialogue box choose Formula Is rather
than Cell Value Is, and enter this formula:

=ISNA(MATCH(A1,B$1:B$6,0))

then click on the Format button, choose the Patterns tab (for
background colour) and choose red. Click OK twice to exit the dialogue
box, and the numbers 2 and 5 should be highlighted.

Then select B1:B6, and click on Format | Conditional Formatting again.
This time use this formula:

=ISNA(MATCH(B1,A$1:A$5,0))

click on the Format button and choose Blue for the background colour,
then OK your way out - the numbers 6, 7 and 8 should be highlighted in
blue.

Hope this helps.

Pete
Click to expand...
 
Top