Duplicates

  • Thread starter Thread starter Dias
  • Start date Start date
D

Dias

Hello!
I am looking for a formula that prevents the entry of duplicates.
Not insert a value in cell B1 when the value already exists in column A.
Any idea?
Thanks
 
Right click sheet tab>copy paste this. Now when you put something in cell b1
it will be added to the end of column A UNLESS it already exists in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address Then Exit Sub
If Not Columns(1).Find(Target) Is Nothing Then Exit Sub
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = Target
End Sub
 
You could select B1
or select col B (if you want it applied similarly for the entire col)

then Click Data > Validation
Allow > Custom
Formula: =COUNTIF(A:A,B1)<1
Click Ok

Test it out
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
This change allows for partial matches
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address Then Exit Sub
If Not Columns(1).Find(Target, lookat:=xlWhole) Is Nothing Then Exit Sub
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = Target
End Sub
 
Hi,

Choose Data, Validation, Custom and enter the following formula

=AND(B1<>A:A)
 
Hi Shane

its doesn't seem to work when I test it


--
HTH

Pls provide your feedback by clicking the YES button below if this posting
is helpful
This will help others to search the results in the archive better

cheers, francis
 
Hi!
Thanks everyone, the simple formula from Max, works perfect for me.
Regards
Dias

Max escreveu:
 
Back
Top