Formula - Helper Column Flag if Duplicate

Options

Is there a formula I can use in a helper column to flag an entry if a duplicate already exist? We are going to share add forms with 100+ people to enter information about their employees. The last time we did this many of them entered duplicate employees causing a lot of chaos.

The goal is to send out alerts/notifications if the helper column detects a duplicate.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Estephania

    I hope you're well and safe!

    Here's an example of how it can be structured. This will show all duplicates (the original and the duplicate(s)).

    Add the formula to a so-called helper column (Checkbox), and update the column name to match yours.

    =IF(ColumnName@row <> "", IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1))
    

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Estephania
    Estephania ✭✭✭
    Options

    Hi @Andrée,

    Thank you for your response. I tried this formula as shown above and am having trouble making it work. See screenshot. I am not sure what I'm doing wrong.

    I have tried changing a few things, but it either doesn't bring any results, or I get errors.

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    edited 09/25/23
    Options

    @Estephania Here is an easier formula for you, =IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0)

    Or you can throw in an iferror too =IFERROR( IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0),0)

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Options

    Hi @Estephania,

    The original formula provided by @Andrée Starå works with the addition of a "=" in the COUNTIF condition.

    =IF([Employee ID]@row <> "", IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1))

    FYI...the "=" is also needed if you use the formula provided by @Eric Law (at least in my testing).

    =IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1, 0)

    Hope this helps,

    Dave

  • Estephania
    Estephania ✭✭✭
    Options

    Thank you @Eric Law and @Andrée,

    I found the reason it was not working from the beginning was that it did not like the numbers in the Employee ID column starting with zero (0). Once I put a letter in front of the number the formulas worked!

    Thank you both!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!