Duplicate Entries in a Column

[kotting]

In my worksheet I have a large area where cells are already "data validated" to only allow entries which are on a predetermined list. BUT I want to make sure the same entry cannot be entered twice in the same column. I also need to ignore some cells.

Compounding this problem is that there are 3 cells in a column, skip a cell, 3 more cells, skip a cell and 3 more.

For example:

E7 - "GF"
E8 - "PT"
E9 - "QI"
SKIP CELL E10
E11 - "KJ"
E12 - "XC"
E13 - "DH"
SKIP CELL E14
E15 - "DJ"
E16 - "BH"
E17 - "SA"

I want to make sure that the value for cell E15 is not the same as E7 (or any other cell in this column.) An optimal solution would be to eliminate the possibility of entering a duplicate value. I must do this for each column of my worksheet. My worksheet is approximately 30 columns wide. But I only want to be sure the entries are not duplicated in the columns (duplicates in the rows are acceptable and expected).

Thank you for helping. I am stumped. AS usual. . . .

[Tim Francis-Wright]

Data Validation should be able to do this.
Select the range that you want (say it's
$E$1:$E$400, and go to Data | Validation
Select Custom validation, and in the
formula use
=COUNTIF($E$1:$E$400,E1)=1

To work with this restriction and using
an allowed list, use
=AND(COUNTIF($E$1:$E$400,E1)=1,COUNTIF(Allowed,E1)=1)

Here, I assumed that the named range Allowed contains the allowable entries.

HTH

[Jay Petrulis]

On 2002-04-12 09:30, Tim Francis-Wright wrote:
Data Validation should be able to do this.
Select the range that you want (say it's
$E$1:$E$400, and go to Data | Validation
Select Custom validation, and in the
formula use
=COUNTIF($E$1:$E$400,E1)=1

To work with this restriction and using
an allowed list, use
=AND(COUNTIF($E$1:$E$400,E1)=1,COUNTIF(Allowed,E1)=1)

Here, I assumed that the named range Allowed contains the allowable entries.

HTH

Hi Tim,

Nice! Great post -- very clever.

Bye,
Jay

[LMF]

Hi Tim, thanks for the nice formula. it works great.

but how can i do validation on a form?

pls let me know

[Tim Francis-Wright]

On 2002-04-12 10:29, LMF wrote:
Hi Tim, thanks for the nice formula. it works great.

but how can i do validation on a form?

pls let me know

There's no direct analog for Data Validation on a form. But that's not to say that you
can't do it.

The BeforeUpdate event has a boolean
variable that keeps the focus on the control
and prevents the Exit or AfterUpdate events
from firing.

I use this to run a validation routine on
textboxes and the like: you can test
for numeric or integral data, or anything
else, e.g.:

Private Sub TextBox1_BeforeUpdate(ByVal TryAgain As MSForms.ReturnBoolean)
If TextBox1.Text Like "foo*" Then
msgbox "...bar!"
TryAgain = True
End if
End Sub

HTH