How to highlight what's different between two columns

[Cesar Urquidi]

Hello,
I have two columns with alphanumeric data ("a" & "b").
The data in the cells is not necessarily in the same row all the time (may
or may not be aligned).
Sometimes one column is longer than the other.
Here's an example:

Column A Column B
10100A 10100A
10200A 10200A
10300A 10300A
10400A 10400A
10500A 10600A
10600A 10700A
10700A 10800A
10800A 10900B
10900A

Is there a way to highlight the cells that are different in the two columns???
Either by using a formula in column "c", or by using conditional formatting
by selecting the whole column from the top ("a" & "b")???

In this example these are the cells that need to be highlighted:

"10500A" (Because it is in "a", but not in "b").
"10900A" (Because it is in "a", but not in "b").
"10900B" (Because it is in "b", but not in "a").

Help please!!!

Thank you.

 

[Bernard Liengme]

In C1 use =IF(OR(A1="",COUNTIF(B:B,A1)),"","missing")
and copy down the column to find which A values are not present in B
In D1 use =IF(OR(B1="",COUNTIF(A:A,B1)),"","missing")

For conditional formatting; select all of A and used
Formula Is =AND(A1<>"",COUNTIF(B:B,A1)=0) and set font of fill colour as
needed
Select all of B and use =AND(B1<>"",COUNTIF(A:A,B1)=0

best wishes

 

[Jacob Skaria]

Check your other post in Worksheet functions

 

[Cesar Urquidi]

Thank you very much!!!
It worked just perfect!!!

Have a good day!
Cesar Urquidi