# Duplicates Highlight with conditional rule for range

Occasional Visitor

# Duplicates Highlight with conditional rule for range

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

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

# Re: Duplicates Highlight with conditional rule for range

=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.

# Re: Duplicates Highlight with conditional rule for range

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