Any workflow to identify duplicates
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
Best Answer
-
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 KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
Answers
-
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.
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
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.
-
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)
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
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!
-
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 KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
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 KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
It worked. Thank you!
-
@Mounika Great news and always happy to help :)
"M"
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives