Formula - Helper Column Flag if Duplicate
Is there a formula I can use in a helper column to flag an entry if a duplicate already exist? We are going to share add forms with 100+ people to enter information about their employees. The last time we did this many of them entered duplicate employees causing a lot of chaos.
The goal is to send out alerts/notifications if the helper column detects a duplicate.
Answers
-
Hi @Estephania
I hope you're well and safe!
Here's an example of how it can be structured. This will show all duplicates (the original and the duplicate(s)).
Add the formula to a so-called helper column (Checkbox), and update the column name to match yours.
=IF(ColumnName@row <> "", IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1))
Would that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
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.
-
Hi @Andrée,
Thank you for your response. I tried this formula as shown above and am having trouble making it work. See screenshot. I am not sure what I'm doing wrong.
I have tried changing a few things, but it either doesn't bring any results, or I get errors.
-
@Estephania Here is an easier formula for you, =IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0)
Or you can throw in an iferror too =IFERROR( IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0),0)
-
Hi @Estephania,
The original formula provided by @Andrée Starå works with the addition of a "=" in the COUNTIF condition.
=IF([Employee ID]@row <> "", IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1))
FYI...the "=" is also needed if you use the formula provided by @Eric Law (at least in my testing).
=IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1, 0)
Hope this helps,
Dave
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!