Thread: Multiple Validations or VBA help - Prevent Duplicates

Hi All,



I am making an excel code that verifies there are no duplicate barcodes(entries).



I just want the entries to go down the rows of column A and a message to pop up if there is a repeat. I currently have a file that checks every new entry with entries above it using data validation and the following function:



=ISNA(VLOOKUP(A10,A$1:A9,1,FALSE))



I want to also check the rows below in case someone accidently skips some rows then another person goes back and fills in the skipped rows.



I have tried a few methods to do this but each has a different problem.



1-I included that the array be in two blocks:



=ISNA(VLOOKUP(A10,(A$1:A9,A11:A$50000),1,FALSE))



This yields an error message and array problem.



2-I have also tried making the equivelent row in column B check for the repeat in column A by putting the formula =ISNA(VLOOKUP(A10,A11:A$50000,1,FALSE)) directly into validation for column B. The problem with this is that there is no message pop-up, I assume because it doesnt deal with the cell the validation is in.



3- I have made the validation formula in terms of column B, and I set column B to equal its equivalent row in column A. As Column A is filled out column B matches it, then the validation should check it for column B.



=ISNA(VLOOKUP(B10,B11:B$50000,1,FALSE))



The problem with this is that my error code won't appear unless I go into column B, activate the cell, then return it.



I would like this procedure to work with only having to input values into column A and an message window popping up if there is a duplicate.





I would consider using Micro's but am not that familiar with it and would need some guidance. ANY HELP is HIGHLY APPRECIATED!!



Thanks so much guys!!

-Scott

I would think a single formula to check the entire column for duplicates.

1) Highlight column A
2) Apply these DV settings:

Allow: Custom
Source: =COUNTIF(A:A,A1)<2


With that, new entries are allowed anywhere in column B, duplicates are not, whether above or below.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.