How to highlight what's different between two columns
 

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.

How to highlight what's different between two columns
 

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

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Cesar Urquidi" <Cesar wrote in message
...
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.

How to highlight what's different between two columns
 

Check your other post in Worksheet functions


"Cesar Urquidi" wrote:

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.

How to highlight what's different between two columns
 

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

Have a good day!
Cesar Urquidi

"Bernard Liengme" wrote:

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

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Cesar Urquidi" <Cesar wrote in message
...
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.

.