Forum Discussion
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
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))>1Adjust the ranges if needed.
Click Format...
Activate the Fill tab.
Select a color.
Click OK twice.
=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.
