Duplicates

[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

 

[Don Guillett]

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

 

[Max]

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

 

[Don Guillett]

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

 

[Shane Devenshire]

Hi,

Choose Data, Validation, Custom and enter the following formula

=AND(B1<>A:A)

 

[xlmate]

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

 

[Dias]

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

Max escreveu:

 

[Max]

Glad it worked. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik