Warning message when Duplicate entry is done through form

2»

Answers

  • Thanks for sharing the formula. I am new to SS and trying to understand the most efficient way to structure formulas. It would be very helpful if you could explain in plain prose what your formula is doing. For your formula to work the base/master sheet will have to be structured a certain way, correct? What form criteria/column is this formula using to decide whether a new form entry is a duplicate? I assume the primary criteria is in column B, right?

    Thanks in advance!


    Sol

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Sol Elisha

    There is no specific structure needed in the sheet for this formula. It's looking into different columns and counting how many times the values in the current row appear in the sheet. If it's more than one time, then this row is a Duplicate!


    So, IF

    the unique combination of: the content of Column A in this current row and the content of Column B in this current row


    Column, Criteria, Column, Criteria

    or

    ColumnB:ColumnB, ColumnB@row, ColumnA:ColumnA, ColumnA@row


    appears anywhere else in the sheet, COUNT how many times there's a row that matches these two criteria.

    COUNTIFS(columns and criteria)


    If the COUNT is >1 or greater than 1, this means there's a Duplicate, so say the word "Duplicate".

    =IF(COUNTIFS(columns and criteria) > 1, "Duplicate",


    If the COUNT is only 1, then this is an Original row.


    =IF(COUNTIFS(columns and criteria) > 1, "Duplicate", "Original")


    For a final formula:

    =IF(COUNTIFS(ColumnB:ColumnB, ColumnB@row, ColumnA:ColumnA, ColumnA@row) > 1, "Duplicate", "Original")


    Now, if you only have one column with a unique value to check through your entire sheet, you only need to list one column and criteria:

    =IF(COUNTIFS(UniqueColumn:UniqueColumn, UniqueColumn@row) > 1, "Duplicate", "Original")


    The criteria is the cell in this current row (or @row) for a specific column. Then you look through that column to see if it appears anywhere else (column:column).

    Does that make sense?

    If you still need help, it would be useful to see a screen capture of your current sheet, but please block out any sensitive data.

    Cheers,

    Genevieve

  • This was very helpful. Is there any way to add a clause if the field I am looking at is blank?

    I have an ID field that I want to check for duplicates but many of the records don't have the ID field populated at all and those I'd like to refrain from checking.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @amyhoppy

    Yes! You can add an IF statement in front of the formula, so if the ID field is blank, return blank, otherwise go through the other formula.

    =IF([ID Field]@row = "", "", IF(COUNTIFS([ID Field]:[ID Field], [ID Field]@row) > 1, "Duplicate", "Original"))

    Let me know if that works for you!

    Cheers,

    Genevieve

  • Hi @amyhoppy

    You could do this via the API where you can precise which rows to ignore etc.

    Below is a short video in which the duplicated records are highlighted, this happens via API (hence the delay) and without any formula in the sheet.


  • Is there now a way to flag if there is a duplicate in the form now?

    I have operations sites rgeistering for our program and I would like it to flag if their ste is already registered in the form.


    Thanks!

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

    Hi @pageella

    I hope you're well and safe!

    Unfortunately, it's not possible now to flag a duplicate in a form, but it's an excellent idea!

    Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

    I hope that helps!

    Be safe, and have a fantastic week!

    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.

  • Chiu Bar
    Chiu Bar ✭✭✭

    @smartsheetdev.io : Can you share on how is that achievable?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!