Welcome!

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?

Find more posts tagged with

Comments

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.