Duplicate Entries in a Column

kotting

New Member
Joined
Mar 18, 2002
Messages
17
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. . . .
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0
Hi Tim, thanks for the nice formula. it works great.

but how can i do validation on a form?

pls let me know
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top