Unique Values

Robert Kalkstein

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?

KrisWalsh

The entire purpose of the Auto-Number column is to accomplish this. What issue are you running into with it?

Robert Kalkstein

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.

KrisWalsh

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!  (-:

Travis

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:

 

 

Robert Kalkstein

Thank you Kris and Travis!

Both very helpful!

Mohd

Hi Travis
Can you please give me formula to check duplicate?

Thanks

KrisWalsh

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)

Mohd

Hey Kris

Thanks a lot