Any workflow to identify duplicates

Mounika

Hi all,

I am creating a contacts directory in Smartsheet, where the form related to this sheet will be completed by team members to enter the contacts that they have found through various sources. However, I am looking for a workflow that identifies the duplicate row (where the contact information is entered twice or more times unknowingly, or two teammates adding the same persons info) and delete that row (based on same First name, last name, country and email address columns) by sending an email alert to admin of that sheet.

Thanks,

Mounika

Marlana K.

@Mounika

The problem with my formula was it is looking at each column and if anything in the column matches then it will return the flag.

Here is another solution that I tested and works much better.

Create a column you can name it what you want Complete Contact etc. for the example I named it Join because that is what we are going to do we are going to Join the information in the 5 columns to create a Contact.

Then change your formula in the Flag column to : =IF(COUNTIF(JOIN:JOIN, JOIN@row) > 1, 1, 0) you will of course need to change JOIN to whatever you name your column.

"M"

Marlana K.

@Mounika

We currently use the following process to identify duplicate request for specific sites in one of our Intake Forms/Sheets.

We created a Duplicate SID Column set to Flag the Duplicate Entry

We used a formula to activate the flag if a duplicate was found

=IF(COUNTIFS(SID:SID, SID@row, Date:Date, <Date@row) > 0, 1

The formula is looking at the SID column and date column because we had prepopulated sites prior to adding intake process. One thing to take into consideration is it has to be an exact match.

Unfortunately there is not a workflow to Delete the actual row but you could duplicate your sheet name it duplicate entries and use the Move a row to another sheet workflow.

Mounika

@Marlana K.

Hi Marlana,

I have the following column names and the form of this sheet will be submitted by various team members, some of whom may have the same contacts. So, I am looking for a way to identify the duplicates and either delete or move to another sheet. For instance, if the first name, last name, country and email are same for the second time submission of same contact information, then that row should be highlighted and moved to new sheet.

Marlana K.

@Mounika

You will need an exact duplicate of your sheet for the Move to New Sheet Workflow

In the Original Sheet you will need to do the following

Create a column "Duplicate Entry" / Colum type Checkbox Set to Flag    

Enter the below formula

=IF(AND([First Name]@row = [First Name]@row, [Last Name]@row = [Last Name]@row, Country@row = Country@row, email@row = email@row), 1, 0)

Go to workflows

Under sheet-to-sheet workflows and select “Move a row to another sheet when specified criteria are met”

Set your trigger

• When rows are changed
• When Duplicate Entry changes to Flagged
• Move rows
• Move to (select you duplicate sheet name)

Mounika

Hi @Marlana K.

Why the column is being flagged for all the entries? Could you please help me set this, the way you did?

I just added a new column, named it as a Duplicate entry and column type was set to checkbox (flag). And have used the formula you provided (I have applied this formula for the column level).

Thank you!

Marlana K.

Hi @Mounika

It might be picking up the blank let me put it in my sheet and see what happens may need to add ISBLANK to it but won't know until I look at it.

Marlana K.

@Mounika

The problem with my formula was it is looking at each column and if anything in the column matches then it will return the flag.

Here is another solution that I tested and works much better.

Create a column you can name it what you want Complete Contact etc. for the example I named it Join because that is what we are going to do we are going to Join the information in the 5 columns to create a Contact.

Then change your formula in the Flag column to : =IF(COUNTIF(JOIN:JOIN, JOIN@row) > 1, 1, 0) you will of course need to change JOIN to whatever you name your column.

"M"

Mounika

@Marlana K.

It worked. Thank you!

Marlana K.

@Mounika Great news and always happy to help :)

"M"