I have the same problem with duplicates. I tried out the formula below and
while it does work if you type the data in manually, I have found it does not
work if you copy and paste.

Is there any way to ensure that if a user decides to paste in a duplicate
record that this will be preveneted?

thanks,
George



"Mike H" wrote:

Sorry I may have misread you post.

If you want to prevent duplicates when you add new data then:-

Select your range from A1 down then

Data|Validation|Formula is

and paste this in

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

This will prevent the addition of duplicates in that range.

Mike

"LO in Sweden" wrote:

Hello!

I have around 2000 articel nr. in a column (A) and add new sometimes.
Then I add new I wish to check so I dont get any duplicate.

Can I do that easy?

Thanks
LO in Sweden

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, 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
---
"George Zervakos" wrote:
I have the same problem with duplicates. I tried out the formula below and
while it does work if you type the data in manually, I have found it does not
work if you copy and paste.

Is there any way to ensure that if a user decides to paste in a duplicate
record that this will be preveneted?

thanks,
George