How to Make one column to accept only unique value

Hi,

How to make one column in the excel worksheet unique, so it can only accept unique value i.e. once written it should not be repeated.

Regards,

Tanny

Try the following...

1) Select your range of cells, for example A1:A100

2) Data > Validation > Settings > Allow > Custom

3) Enter the following formula:

=COUNTIF($A$1:$A$100,A1)=1

4) Click Ok

If you'd like a custom message to appear when invalid data is entered, select the 'Error Alert' tab and fill-in the necessary fields.

Hope this helps!

Yes, unfortunately, Data Validation doesn't work if you paste data into your range. As far as disabling the paste function, I don't know whether that's possible. If so, it would likely have to be done using VBA. Hopefully, someone with VBA experience will be able to help.

Meanwhile, you may want to look at the following link for alternatives...

http://www.cpearson.com/excel/duplicat.htm

While these methods won't prevent one from entering a duplicate entry, they will flag or highlight them.

Hope this helps!

Some code similar to this should help. Enter the VB Editor by right-clicking on the sheet tab of the relevant sheet and select 'view code'. Then paste this.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto Finish

If Target.Column = 1 Then
For Each cell In Selection
If Application.WorksheetFunction.CountIf(Columns(1), cell.Value) > 1 Then
Application.Undo
MsgBox "You were trying to paste in duplicate entries"
End If
Next cell
End If

Finish:

Application.EnableEvents = True

End Sub

These lines in the code set the column to do the duplicate check on

If Target.Column = 1 Then
If Application.WorksheetFunction.CountIf(Columns(1), cell.Value) > 1 Then

1 currently represents Column A. Change the number to suit the column you want to check.

Cheers!

Thanks again, it works.

Regards,
Tanny

Hi Richard / Domenic ,

It works fine , if i copy one cell and paste in that column it throws error... But if I copy more that one cell i.e. 2 or 3 values. it is able to paste and does not check uniqueness. Could you please provide any solution to this problem.



Regards,

Tanny