Highlighting duplicates

Tjmarget

Hi all,

I am trying to create a sheet that will highlight a duplicate entry in the Les Olsen ID column. I researched the subject and found the advice to use a formula like this =IF(COUNTIF(Column:Column, Column@row) >1,1) but when I try to apply it to my sheet it shows up as UNPARSEABLE. Can anyone tell me why this isn't working? I appreciate your expertise!

Jason Tarpinian

=IF(COUNTIFS([Les Olson ID]:[Les Olson ID], [Les Olson ID]@row) > 1, 1)

Write it like above, your range is only looking at a single cell, you want it to count the entire column. Your criteria [Les Olson ID]1@row is incorrect syntax, you can either reference a row number or @row, not both.

Tjmarget

I see. I removed the 1s after Les Olsen ID but now I get #Circular Reference.

Andy_B

Hi @Tjmarget ,

The reason you're getting the #CIRCULAR REFERENCE error is because you are asking the formula to output data in the same column where it is looking for input. If you use the same formula:

=IF(COUNTIFS([Les Olson ID]:[Les Olson ID], [Les Olson ID]@row) > 1, 1)

in a separate helper column, convert it to a column formula and it should output a 1 whenever there is a duplicate. You can also use the same formula in a checkbox type column if that would better suit your purposes.

Once the formula is set up in the helper column, add a new conditional formatting rule. Depending on your column type, use one of the following:

• If Helper Column is 'checked', apply [highlight color] to the Les Olson ID column

or

• If Helper Column is equal to 1, apply [highlight color] to the Les Olson ID column

Once this is set up, you can right click on the helper column and hide it. The only thing you will see now is the highlighted duplicates.

Hope this helps! 😄

Tjmarget

That makes sense! :) I got it to work. Thank you so much for the help!