Conditional Formatting to highlight customers with duplicate values in another cell

[matratus34]

Hi - I have a list of customer IDs in Column A on my spreadsheet and in column A there is a Y or N flag for each customer.

The customer ID can appear on numerous rows but there should never be more than 1 Y in adjacent cell.

For example: if customer 100 appears on 6 rows then 5 rows should have a N in the adjacent cell and only 1 should have a Y
(There can also be zero Ys)

Could you please help me with some conditional formatting to highlight the customers where there are duplicate Ys?
I have tried but with no success.

In the example below I could want to highlight customer 3 as there are 2 Ys against it.

As always any help is greatly appreciated

A B
[TABLE="width: 185"]
<tbody>[TR]
[TD][TABLE="width: 199"]
<tbody>[TR]
[TD]Customer ID[/TD]
[TD]Offer[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Select column A, click Conditional Formatting > New Rule > Use a formula and enter:

=COUNTIFS(A:A,A1,B:B,"y")>1


Or maybe select column B and use this rule:

=AND(B1="y",COUNTIFS(A:A,A1,B:B,"y")>1)

which would highlight just the y's that are extra.

 

[matratus34]

That's great - thank you

 

[Eric W]

You're welcome.