Highlighting duplicates

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!

Answers

  • Jason Tarpinian
    Jason Tarpinian Community Champion

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

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Tjmarget
    Tjmarget ✭✭✭

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

  • Andy_B
    Andy_B ✭✭✭✭
    edited 03/12/25

    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
    Tjmarget ✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!