Enforce unique values in the primary column

Garan Keeler

I want to maintain a sheet of products where the primary column stores the serial number of the product. By definition, the serial number should be unique and not allowed to be duplicated in a sheet. I can't find any options in Smart Sheet to enforce this. Is it possible?

David Joyeuse

Hi @Garan Keeler

As far as I know, there's no option to enforce unique values in any column in Smartsheet. But there's some workaround to do this.

You could have an helper checkbox type column for example with this formula within it:

=IF(COUNTIF([Main Column]$1:[Main Column]@row, [Main Column]@row)>1, 1, 0)

The box will be checked if the value in the Main Column already appeared in the above rows.

Then, you can either create a report and only displays rows where this box isn't checked. Or also create an automated workflow to move the rows where the box is checked. Keeping the sheet free of any duplicate.

Hope it helped.

Robert Charles

On second thought. Deleted my comment :)

hammadamjad

Using below steps..

added new column with Check Box

But after that i am not getting any option of adding formula in the box. Kindly suggest steps so we can add something that allow us to restrict user adding duplicate Sr No

BSADK

@David Joyeuse That solution probably works in 99% of cases. In fact, it works in most of my sheets. However, it doesn't work with one of my sheets. The problem is, just like other various spreadsheet programs, the COUNTIF function is NOT case sensitive.

I have a column that uses a generated ID - it is an alphanumeric ASCII ID generated sequentially, including both lower and uppercase letters, and so this incorrectly flags some of my IDs as duplicates when in fact, they are not. They have one or more upper case letters in one ID that match one or more lower case letters in the other. These IDs are copied over from the system that generates them and it's hardcoded into that system, so I can't change it (as silly as I think the system is) otherwise I would.

Does anyone have a case sensitive solution? Other spreadsheet programs have the EXACT function which is case sensitive. It doesn't work exactly the same as COUNTIF, but for example If Smartsheet had that function, I could use the formula:

==IF(SUM(EXACT([Main Column]$1:[Main Column]@row, [Main Column]@row)>1, 1, 0))

and it would work. However, it doesn't appear that EXACT is supported in SmartSheet.

Does anyone have a case sensitive solution?

BSADK

I (sort of) found a case sensitive solution that ALMOST works. The FIND function is case sensitive, and you can combine strings across a range with the JOIN function. So, I came up with this formula:

=FIND([Main Column]@row, JOIN([Main Column]:[Main Column]), FIND([Main Column]@row, JOIN([Main Column]:[Main Column])) + 1)

The inner FIND finds the first instance of a match (it should at least find the value from the row you're on), then the outer FIND will only return a value if there's another instance of a match, otherwise the whole thing returns 0.

This worked until row 276 of my sheet, then it broke and wasn't detecting any duplicates after that. So, I broke out the components of it into separate columns to figure out why. One column was my inner FIND function. That had interesting results.

Row 276 found a match at character 3975, then everything after was 0. The FIND function wasn't even finding its own row anymore. Issue is, Smartsheet has a 4000 character limit - not just on what it will show in a cell, but even a string variable in a calculation can have no more than 4000 characters.

So, if you know you'll never have more than 4000 characters, my equation above works. Otherwise, I'm still looking for a case sensitive solution...

Also, d'oh! I put a double equals sign at the beginning of my comment above, it should only be one and I'm past the hour restriction so I can't go back and edit it now...

jhayeseu

I'm trying to do a similar request but I want it to work off a status column I have. I have the following code and it doesn't seem to be working. Can anyone assist me? When I hit enter, the brackets around the status column disappear and I really can't figure out why.

=IF(COUNTIFS([EE ID#]@row, [EE ID#]@row, [Status]@row, CONTAINS("Completed", [Status]@row) > 1, 1, 0))

TT 314

This is absolutely ridiculous, that you need a complicated workaround to enforce unique values in any column. Smartsheet, once again, is a rather poor tool for almost anything.