Alert when a duplicate entry is made

robin_inno

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.

Peggy Parchert

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

robin_inno

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.

SteyJ

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.

Peggy Parchert

Hello @robin_inno

The beginning of your formula is duplicated. Remove the extra [Bin #]@row <>

https://community.smartsheet.com/discussion/comment/410376#Comment_410376

Hope this helps.

Peggy

robin_inno

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.

Peggy Parchert

@robin_inno

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