Duplicates Highlight with conditional rule for range

Copper Contributor

Hello, I would like to make a calculation or find a way to do this: 

AKampianakis_1-1649402524247.png

 

in the picture above I have 2 different columns. 1 is Machines , and the other is Product. I would like to highlight the duplicates that are made by both machines (have at least once A1 and/or A2 in column A in the range of the duplicates) 
Something like (pseudocode style syntax here..!) if duplicates then check range of duplicates, if within that range there's A1 and A2 then highlight cell C or whole row. 
Is there a way to do this in Excel?
Thanks!

2 Replies

@AKampianakis 

=AND(COUNTIFS($B$2:$B$11,$B2,$A$2:$A$11,"A1")>0,COUNTIFS($B$2:$B$11,$B2,$A$2:$A$11,"A2")>0)

Maybe with this rule for conditional formatting.

@AKampianakis 

Select the range you want to format conditionally, for example C2:C11, or rows 2 to 11 in their entirety.

The active cell in the selection should be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=SUM(--(LEN(UNIQUE(FILTER($A$2:$A$11,$B$2:$B$11=$B2,"")))>0))>1

Adjust the ranges if needed.

Click Format...

Activate the Fill tab.

Select a color.

Click OK twice.