Compare cell values in Column based on cell values in adjacent column

[harrysolomon]

I need to do a comparison of values in one column on a worksheet based on the values in an adjacent column of the same worksheet. The specific scenario is as follows.

The code should compare the values in Column B, based on the corresponding values listed in Column A. Discrepancies in the Column B values should be highlighted.

In this example, the value XXX appears in Column A in Rows 2 - 4, meaning the corresponding values in Column B on Rows 2 - 4 should be compared to each other to find the non-matching value and as a result Red (B4) should be highlighted.

Then for Rows 5 - 6 where YYY appears in Column A, the corresponding values in Rows 5 - 6 in Column B should be compared to each other. In this case no discrepancy will be found so nothing should happen in Rows 5 - 6 in Column B.

And then the same thing for Rows 7 - 9 with the ZZZ value and the corresponding values in Column B Rows 7 - 9. The value Orange (B7) should be highlighted after B7 – B9 are compared to each other.

Thanks in advance for any help with this issue.
harrysolomon

Sample workbook.xls
	A	B
1	Column A	Column B
2	XXX	Green
3	XXX	Green
4	XXX	Red
5	YYY	Blue
6	YYY	Blue
7	ZZZ	Orange
8	ZZZ	Yellow
9	ZZZ	Yellow
Sheet1

 

[jtakw]

Hi,

Based on your sample and description, use this formula for Conditional Formatting:

Book3.xlsx
	A	B	C
1	Column A	Column B
2	XXX	Green	FALSE
3	XXX	Green	FALSE
4	XXX	Red	TRUE
5	YYY	Blue	FALSE
6	YYY	Blue	FALSE
7	ZZZ	Orange	TRUE
8	ZZZ	Yellow	FALSE
9	ZZZ	Yellow	FALSE
10	AAA	White	FALSE
Sheet732

Cell Formulas
Range	Formula
C2:C10	C2	=AND(COUNTIF(A$2:A$10,A2)>1,COUNTIFS(A$2:A$10,A2,B$2:B$10,B2)=1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(COUNTIF(A$2:A$10,A2)>1,COUNTIFS(A$2:A$10,A2,B$2:B$10,B2)=1)	text	NO

 

[harrysolomon]

Both of these options work perfectly.
Thanks so much for the quick help.

harrysolomon

 

[jtakw]

Your welcome, thanks for the feedback.
Actually, they are the Exact same formula, I just show it as CF to show the highlight, and as Formula in column C to show the Logical results.

 

[harrysolomon]

Ah OK, got it. I see it now.
Sorry for the confusion.