VALIDATION FOR A PARTICULAR COLUMN

[Guest]

In one of the column the same data should not be repeated while
coping and should prompt or give a error message. OR

How can i ensure that the same data is not repeated while coping
in a particular column? OR ANY OTHER WAY TO KEEP CONTROL ON SAME SET OF DATA
IN A PARTICULAR COLUMN Pl help.

 

[Guest]

Highlight the entire column and click Data->Validation
Under "Allow" select Custom
Under "Formula" enter the following
=COUNTIF(A:A,A1)<=1
Replace all A's with the column selected. i.e. enter this formula for
column C...
=COUNTIF(C:C,C1)<=1

 

[Guest]

Try Vasant's code below, which will prevent duplicate entries -- including
preventing copy-paste or dragging which would defeat data validation -- for
col A in a sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Max]

That's what was essentially suggested to the OP in his earlier posting, but
I think OP's quite firm <g> in wanting it to be also resistant to copy n
paste or drag as well, both of which would defeat data validation.