Prevent Duplicates Using Data Validation

opeyemi1 · Oct 5th 2004

Hello all:

I am putting a list together in excel and I have items in columns A to D.

If I enter an item in column a and I press enter, is there a way that excel could search column A to see if the number has been previously entered, if it's been previously entered, can I get a prompt saying "number already exists" or something like that?

Thanks so much for your help.

fifijazz · Oct 5th 2004

After highlighting your input range, let's say A1:A500. Choose Data>Data Validation from the menu. On the Settings tab: Allow = Custom.

In the formula box: =COUNTIF(A1,A$1:A$500)=1. (Watch the $ signs.) Type your warning message on the Error Alert tab.

[rdv]*[/rdv]

mhabib · Oct 5th 2004

Validation will do the job for you.

1. Select the entire column A (or whichever column you'd like to restrict)
2. Click Data > Validation
3. In the "Allow" Dropdown, select "Custom"
4. Type the condition =COUNTIF($A:$A,A1) <=1
5. Click OK

HTH

m

PS: Have a look at the attached example

opeyemi1 · Oct 5th 2004

Thanks much fifijazz & mhabib

Your methods worked!

Quote from mhabib

Validation will do the job for you.

1. Select the entire column A (or whichever column you'd like to restrict)
2. Click Data > Validation
3. In the "Allow" Dropdown, select "Custom"
4. Type the condition =COUNTIF($A:$A,A1) <=1
5. Click OK

HTH

m

PS: Have a look at the attached example

Display More

Barbarr · Oct 5th 2004

Check out http://www.ozgrid.com/Excel/Formulas.htm#VALIDATION for more information regarding Data Validation and Conditional Formatting in OzGrid's Handy Hints for Excel Spreadsheets.

Prevent Duplicates Using Data Validation

Participate now!

Share