Highlighted
New Contributor

# Trying to highlight duplicates

Hi there.

I am trying to come up with a formula that will allow me to highlight duplicate cells but only if it is a duplicate for a value in another column as well.

What I mean is:

I want the values in column B to be highlighted if they are the second duplicate for the values in column A.

So the only cell that should be highlighted should be B6

I know you can create a 3rd column and concat column A & B into 1 unique value and then find duplicates there, but I'd prefer not to add columns

So the end result would look like this

Any help would be appreciated!

5 Replies
Highlighted

# Re: Trying to highlight duplicates

You may apply such rule

with formula

``=(COUNTIFS(\$A\$1:\$A1,\$A1,\$C\$1:\$C1,\$C1)=2)*(\$C1<>"")``
Highlighted

# Re: Trying to highlight duplicates

@Sergei Baklan Thanks a lot for your response!

What would the formula be to make it highlight every duplication after the first? It looks like right now it only highlights the second instance but not the third, fourth, fifth etc

Highlighted

# Re: Trying to highlight duplicates

For this please change =2 in rule formula on >=2, i.e.

=(COUNTIFS(...)>=2)*(...)

Highlighted

# Re: Trying to highlight duplicates

@Sergei Baklan Thanks so much! I really appreciate it!

Highlighted

You are welcome