Duplicate Formula

Natalie11

I have a Smartsheet which is fed from a form an Intake team is using to gather prospect information and we are having issues with duplicate entries.

The sheet/form has a field for prospect first name and another for prospect last name which we would like to check for duplicate entries of.

I created a column combining both first and last name and another column for a formula to flag duplicates using that full name column, however, I'm having trouble with the formulas I've tried.

Please help :)

Jennifer Kurtz

Hi @Natalie11 !

You could use the "COUNTIF" function to help identify duplicate values (in this case, names). You mentioned creating a column to "flag" duplicates - so it sounds like you may have a checkbox column of sorts set up already where you'd put the formula? If so, it could look like this: (you'll need to change the column names, of course :> )

=IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row)>1, 1, 0)

The COUNTIF syntax is (Criteria_Range, Criteria) - so here, the formula is looking at the whole column where you have the full name, and comparing it to the name in that row to see if the total is more than 1. Then, we wrap that in an IF statement to use in a checkbox type column.

Does that work?

Jennifer Kurtz

Hi @Natalie11 !

You could use the "COUNTIF" function to help identify duplicate values (in this case, names). You mentioned creating a column to "flag" duplicates - so it sounds like you may have a checkbox column of sorts set up already where you'd put the formula? If so, it could look like this: (you'll need to change the column names, of course :> )

=IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row)>1, 1, 0)

The COUNTIF syntax is (Criteria_Range, Criteria) - so here, the formula is looking at the whole column where you have the full name, and comparing it to the name in that row to see if the total is more than 1. Then, we wrap that in an IF statement to use in a checkbox type column.

Does that work?

Natalie11

@Jennifer Kurtz Yes, it worked! Thank you so much for your help. :)

Jennifer Kurtz

Happy to help! Have a great afternoon!