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 tabcopy 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dias" wrote in message
...
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

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
---
"Dias" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Right click sheet tabcopy 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dias" wrote in message
...
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

Hi,

Choose Data, Validation, Custom and enter the following formula

=AND(B1<A:A)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Dias" wrote:

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

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


"Shane Devenshire" wrote in
message ...
Hi,

Choose Data, Validation, Custom and enter the following formula

=AND(B1<A:A)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Dias" wrote:

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

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

Max escreveu:
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

Glad it worked. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Dias" wrote in message
...
Thanks everyone, the simple formula from Max, works perfect for me.
Regards
Dias