Duplicates in Column

Options

I used the formula below in my sheet and it seems to be working alright except that it's coming back with a couple saying they're duplicates but when I use control find to see if that eID is a duplicate on my sheet, it's the only one. Is there something wrong with my formula?

=IF(COUNTIF(eID:eID, eID@row) > 1, 1, 0)


Best Answer

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    Hello @MindyM

    Happy Holidays! I would add a helper column (Column Type: # auto number) with no specifics. This can be hidden after created. Then enter the below formula into your Duplicate (Column Type: checkbox).

    =IF(eID@row <> "", IF(COUNTIFS(eID:eID, @cell = eID@row, helper:helper, @cell <= helper@row) >= 2, 1, 0))

    Hope this helps.

    Peggy

  • MindyM
    MindyM ✭✭
    Options

    Hi @Peggy Parchert!

    Happy Holidays! Thank you! That worked but the issue I have now is how do I get the helper column to add in the numbers all the way down? I set it up to do auto # but I can't pull down and have the numbers advance it just copies 1 all the way down the column. I have over 1,000 rows and I'm sure there's an easier way than me typing 1, 2, 3, ,4 in each row.

    Thanks again!

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @MindyM

    The helper column is an # auto number column type. It should populate for all existing rows and any new rows. You might have to refresh your sheet to see the population.

    Peggy

  • MindyM
    MindyM ✭✭
    Options

    Oh my, I feel special. I wasn't sure how to make that work after you changed the column type. Thank you! That worked!

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @MindyM - happy to help - have a great holiday!

    Peggy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!