Compare rows based on 3 columns and highlight when column1=column2, but column 3 is different

[JohannaL]

I need your excel help! – if I have three columns, I want to highlight the rows where the values in column 1 and 2 match, but are different in column 3. For instance:

[TABLE="width: 168"]
<colgroup><col width="56" span="3" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]A[/TD]
[TD="class: xl65, width: 56"]1[/TD]
[TD="width: 56"]xx[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65"]1[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65"]2[/TD]
[TD]xa[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65"]2[/TD]
[TD]da[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl65"]3[/TD]
[TD]zz[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl65"]3[/TD]
[TD]zz[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl65"]3[/TD]
[TD]zy[/TD]
[/TR]
</tbody>[/TABLE]

Here row 1 and 2 match in column 1 and 2, and also column 3 – everything ok
Row 3 and 4 also match in column 1 and 2, but column 3 does not – highlight
Row 4,5,6 - match in column 1 and 2, but column 3 is not the same for all 3 - highlight

If column 1&2 do not match, column 3 is irrelevant and no highlighting is necessary.

Do you know how to do this? That would safe me A LOT of pain and manual labor

 

[Eric W]

Assuming your columns are A, B, and C (if not, change them in the formula), select column A. Then click on Conditional Formatting --> new rule --> Use a formula to determine which cells to format.
In the formula box, type:
=(A1=OFFSET(A1,-1,0))*(B1=OFFSET(B1,-1,0))*(C1<>OFFSET(C1,-1,0))
Then click Format... and select a highlight color.

 

[JohannaL]

Thanks!! Works perfectly