Alert when a duplicate entry is made
I'm trying to set up an automation that alerts when a duplicate entry is made. I am not having any luck utilizing the =IF[Countif] formulas and I'm looking for some assistance. In my sheet I am looking to flag duplicat Bin#'s in column 1.
Answers
-
Hello @robin_inno
Happy Holidays! I would add a helper column (Column Type: # auto number) with no specifics. This can be hidden after created. Then enter this formula into your Duplicate Yes/No (Column Type: Dropdown).
=IF([Bin #]@row <> "", IF(COUNTIFS([Bin #]:[Bin #], @cell = [Bin #]@row, helper:helper, @cell <= helper@row) >= 2, "Yes", "No"))
You could setup conditional formatting to highlight if the Duplicate Yes/No column is Yes.
Peggy
-
Hi Peggy! Thanks for your assistance. I made the helper column and hid it, as you suggested. I am having a hard time with the formula - getting some colored brackets. If you know how the fix this, that would be great. Otherwise I'm back where I started.
-
try this in the [Duplicate Yes/No] column.
=IF(COUNTIF([Bin #]:[Bin #], [Bin #]@row) > 1, 1)
Convert [Duplicate Yes/No] column to a checkbox
Setup automation for when row changes in duplicate column to 1 to alert someone, or use conditional formatting to highlight the row.
Sincerely,
Jacob Stey
-
Hello @robin_inno
The beginning of your formula is duplicated. Remove the extra [Bin #]@row <>
Hope this helps.
Peggy
-
Still giving me issues. I'm about to give up on the ability to see duplicates. Nothing anyone has suggested actually works within the sheet.
-
Your formula isn't correct, you have an extra [Bin #]@row - enter this:
=IF([Bin #]@row <> "", IF(COUNTIFS([Bin #]:[Bin #], @cell = [Bin #]@row, helper:helper, @cell <= helper@row) >= 2, "Yes", "No"))
Peggy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!