Warning message when Duplicate entry is done through form

Sol Elisha

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.

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

amyhoppy

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.

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

smartsheetdev.io

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.

https://www.youtube.com/watch?v=EXI6iFN6Q2I

amyhoppy

https://community.smartsheet.com/discussion/comment/334753#Comment_334753

So easy, thank you!!!

pageella

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å

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!

Chiu Bar

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