Ensuring data is unique

[tsmithers]

I have a column of text (alpha/numeric) of reference numbers. I need to stop anyone entering the same reference number twice in a different cell below the first entry or anywhere within that column.

 

[Norie]

Take a look at Data>Validation...

You can enter a formula, perhaps a COUNTIF, if you use the Custom option.

 

[just_jon]

I have a column of text (alpha/numeric) of reference numbers. I need to stop anyone entering the same reference number twice in a different cell below the first entry or anywhere within that column.

Welcome to MrExcel - use data validation.

Click on the column - we'll assume column A - then

Click Data | Validation

In the Allow box, select Custom

In the Formula box, enter: =COUNTIF(A:A,A1)<2

Make sure Ignore Blank is checked.

Now, no entry can be entered twice.

 

[Zack Barresse]

Hi there tsmithers, welcome to the board!

Assuming that your data range of interest is A1:A10 and A1 is a header row, perform the following ..

1) Select A2:A10
2) Data | Validation | Custom..
3) =COUNTIF($A$2:$A$10,A2)=1
4) Enter any error messges if you'd like, Ok.

Notice the relative reference of A2, that means that A2 is the selected (off-color) cell when you selected your range (clicked A2, then dragged down to A10 and released mouse). Now if you enter "a" in A2, then you cannot enter it in A3:A10, and vice versa if you entered "a" in A5, then you cannot enter it in A3, etc.

HTH

Edit: Too slow for Jon!

 

[tsmithers]

Thanks guys. Not sure if I clicked on the wrong thing but I didn't mean to report someone. Was trying to say thanks for the post as its provided me with the answer

 

[Norie]

Thanks guys. Not sure if I clicked on the wrong thing but I didn't mean to report someone. Was trying to say thanks for the post as its provided me with the answer

I bet it was me you reported.

That's happened before, perhaps I'm doing something wrong.

 

[Zack Barresse]

No worries. If it comes up and nothing warrants moderation, the Moderator's leave it be and clear the error. No harm no foul.

And Norie doesn't get reported that often.

 

[Norie]

And Norie doesn't get reported that often.

Now I'm getting paranoid.