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.

 

[Jacob Skaria]

1. Select column A or Range (say A1:A10). Please note that the cell reference
A1 mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(A1<>"",COUNTIF(B:B,A1)=0)

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK
6. Similarly select column B and apply CF formula
=AND(b1<>"",COUNTIF(a:a,b1)=0)

------------------------------------------------------------------------------------
--Using one conditional formula..Select both Columns A and B. Please note
that the cell reference A1 mentioned in the formula is the active cell in the
selection. Active cell will have a white background even after selection

=AND(A1<>"",COUNTIF($A:$B,A1)=1)


If this post helps click Yes

 

[Cesar Urquidi]

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

Have a good day!
Cesar Urquidi