VALIDATION FOR A PARTICULAR COLUMN
 

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.

VALIDATION FOR A PARTICULAR COLUMN
 

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

"Vijay Kotian" wrote:

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.

VALIDATION FOR A PARTICULAR COLUMN
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vijay Kotian" wrote:
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.

VALIDATION FOR A PARTICULAR COLUMN
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sloth" wrote in message
...
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