Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Unique Values
I searched Help and the Community for "unique value" and did not find anything.
Is there a way for a field in a sheet to be unique?
For example, I want the Document ID column to be unique. Let's say I do not want to use the Auto Number property. Is there a way to ensure that all values are unique in a column when anyone enters a value?
Comments
-
The entire purpose of the Auto-Number column is to accomplish this. What issue are you running into with it?
-
We started using the Auto-Number, but sometimes records would get created by users by mistake. Then, we would have to delete the Auto-Number column, delete the erroneous records, re-add the Auto-Number column and the Auto-Number would be fine.
We were thinking to manually control the ID #, but I wanted to check if I can protect against duplicate values in the field.
-
Example Sheet - Feel free to play around with it.
The purpose of the Auto-Number column is to give you a unique value.
When someone mistakenly adds a row & you delete it, the next value will be one higher.
Row 9 - Good Entry - ID# 10009
Row 10 - Mistaken Entry - ID# 10010
Row 10 Deleted
New Row 10 - Good Entry - ID# 10011
I played around with it in the Example Sheet to double check. I added and deleted columns and always get a new, unique, higher value for the new row on the sheet.
Unless you want/need your ID #'s to be exactly sequential, I can't see a reason to try something else. (like a formula that could break)
Hope this helps! (-:
-
One option is to add a column with a formula that checks if the corresponding ID is a duplicate, then use conditonal formatting to color the ID background if it is a duplicate.
Here's an example:
-
Thank you Kris and Travis!
Both very helpful!
-
Hi Travis
Can you please give me formula to check duplicate?Thanks
-
You can hover over his embeded sheet & see the formula.
He also used Conditional Formatting to make the ID column turn red.
Travis' Formula:
=IF(COUNTIF(ID:ID, ID1) > 1, 1)
If your column name is "ID #" *has a space in the name*
=IF(COUNTIF([ID #]:[ID #], [ID #]1) > 1, 1)
With absolute reference to column. *best practice*
=IF(COUNTIF($[ID #]:$[ID #], $[ID #]1) > 1, 1)
-
Hey Kris
Thanks a lot
