Prevent duplicate data entries in Excel 2000

September 7, 2009 at 10:08:01
Specs: Windows XP
When entering an AlphaNumeric in COL A, I would like to check contents of all previous cells in COL A for IDENTICAL entry, and warn or prevent continuing.
Cannot have DUPLICATES in COL A. Entries will grow into the hundreds.
I don't see anything about "DUPLICATE" or "UNIQUE" anywhere in Excel 2000.
I am new to macros.
Do you know how to do this?

See More: Prevent duplicate data entries in Excel 2000

Report •

Report •

September 7, 2009 at 13:28:05
Very good Mike!
The first link made no sense at all but the 2nd link was written by someone who knows his beans.
The logic was there and it was so clear that I have been able to modify it to work in my situation.
Give me a few more years, and I will be an advanced beginner!

Again, thanks you very much. Your quick response is very much appreciated.


Report •

September 7, 2009 at 13:53:25
re: The first link made no sense at all...

Maybe not to you, but I implemented Chip Pearson's solution in a matter of seconds. It works just as described.

re: ...but the 2nd link was written by someone who knows his beans.

I hope you're not implying that Chip Pearson doesn't "know his beans". Chip Pearson is one of the premier Excel and VBA for Excel experts around.

Perhaps that explains his ranking on a Google search for Excel Formulas. It would serve you well to spend a few minutes hours browsing through his site, especially this page:

Report •

Related Solutions

September 8, 2009 at 08:14:13
We are clearly at different levels.

What I meant by my response about the first one was that I didn't get it. While the second method (for me) fell right into place and I just understood what he was doing and followed the code and it worked first time.

I think that based on our individual experiences, we all follow or don't follow a particular explanation by someone (instructor). And for those that don't (frequently me) an additional phrase or re-phrasing will suddenly result in the "AH-HA!" moment.

Hey, I am not dead yet so certainly I continue to learn. I will indeed look at that page on and undoubtedly pick up a lot more.

Thanks again.


Report •

September 8, 2009 at 08:43:55
Please understand that this is not a push-back; I'm just trying to understand what you found different between the 2 sites.

An explanation may help me word my future responses to questions in a way that will help more people.

Both Chip Pearson and the author at Mr. Excel use the Data Validation feature to present a message if duplicate data was entered in a given range. They both use a formula that evaluates to either True or False and they both present the message if the formula evaluates to False.

The only difference is the actual formula used.

If we want to take this a level deeper, one could argue that Chip Pearson's method is actually the better way since Excel only has to evaluate a single function, not two. (Note: I have nothing against Mr. Excel and have sought and used information at that site many times.)

Once Mr. Pearson's function evaluates the COUNTIF function, it's done. Mr Excel's method requires Excel to first evaluate the VLOOKUP function and then evaluate the ISNA function.

While this inefficiency may be minor in this singular case, a multitude of these types of functions could impact the performance of a larger workbook.

All that said, I'd be interested to hear why the method used at the Mr. Excel site was easier for you to follow.

Report •

Ask Question