I need till check for duplicate in a column.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 don’t get any duplicate.

Can I do that easy?

Thanks
LO in Sweden
 
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
 
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
 
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
 
The former did not work for me :-(
... to star twith.

But now I know way

Every time a treed a formula with , (Comma) I had problem…
Now I use ; (semicolon) and everything thing works. :-)

Probably I don’t use “standard†Regional and Language Options for Windows

Thankt for the formula.

LO in Sweden




"Mike H" skrev:
 
Back
Top