If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. . |
Thread Tools | |
Display Modes | |
|
|