Prevent Duplicates

adp

Screenshot 2023-11-14 111113.png

Hello, I have a column name PO Number for example. This is a text/number column. The value I will enter in this column is unique and should not be repeated. Is there any way to accomplish this? Say I am entering Serial Numbers, and if I enter a repeated serial, or value, can smartsheet block it, or tell me I am entering a duplicate value, or at least mark it as a duplicate? I am planning to enter these values either directly on the sheet or thru a form.

I have read about creating a DUP Column and checkmark, if is dup it will mark it, if it is blank then it will unmark it, can it be done? Putting two formulas for one column?

Thank you very much,

Alain

Joe Goetschel

Try this! I do this all the time

Step 1: I would make it a new column -> checkbox Flag. and name it "Duplicate Entry".

Step 2: put in the fomula =IF(COUNTIF([Column name]:[Column name], [Column name]@row) > 1, 1, 0)

Step 3: Optional setup conditional formating to highlight rows red. / setup Automation to notify you etc.

Joe Goetschel

You can add "ISBLANK" to the formula

=IF(ISBLANK([Column name]@row),0,IF(COUNTIF([Column name]:[Column name], [Column name]@row) > 1, 1, 0))

Joe Goetschel

Try this! I do this all the time

Step 1: I would make it a new column -> checkbox Flag. and name it "Duplicate Entry".

Step 2: put in the fomula =IF(COUNTIF([Column name]:[Column name], [Column name]@row) > 1, 1, 0)

Step 3: Optional setup conditional formating to highlight rows red. / setup Automation to notify you etc.

adp

Thank you very much. Let me try this now.

adp

Screenshot 2023-11-14 111118.png

So I tried it, and it works. However, when I manually delete the cell with the duplicate, the flag stays on, is there a way that of the value is blank it will uncheck the flag?

Actually, I cannot even uncheck the flag manually, it stays checked with no value in PO Number.

Nick Korna

Hi @adp,

A helper column can certain do this with a column formula like:

=IF(COUNTIF([PO Number]:[PO Number], [PO Number]@row) > 1, "Yes", "No")

Any duplicate lines would show up with Yes (this would include the original).

Alternatively you can use a formula like this to exclude the original:

=IF(COUNTIF([PO Number]$1:[PO Number]@row, [PO Number]@row) > 1, "Yes", "No")

The downside with this is that you can't use it as a column formula and have to pull/drag it down.

You can adapt these to other outputs obviously (e.g. ticking a box in a checkbox type column) and/or combine with conditional formatting to make them more apparent (highlighting cells/rows, for example).

Hope this helps give you some ideas, but if you've any problems/questions then just post them. 🙂

Joe Goetschel

You can add "ISBLANK" to the formula

=IF(ISBLANK([Column name]@row),0,IF(COUNTIF([Column name]:[Column name], [Column name]@row) > 1, 1, 0))

adp

Thank you so much.

CAS the CSA

Is there anyway to do this with the original row not having the same flag and just the newer duplicate(s)?

Joe Goetschel

@CAS the CSA I will need to think through that one! If you are using an auto-numbering column as a helper, then I'm sure we can do something. If I come up with it tomorrow I will update this post!