Excel Valid Values Data Validation

Microsoft Microsoft excel 2007 (pc)
February 3, 2010 at 12:58:17
Specs: Windows 7
Hello All,

I need to be able to paste several rows of text formatted data into a column (field) and have the data validated by comparing to a worksheet with Valid Values. The text in the column has 62 rows of names. What I need is for a macro or VBA routine to compare the entered names with the 62 names in the valid values worksheet, then if any imported/pasted text is misspelled or missing, an error window will pop-up to identify the error in validation. In addition, the number of rows could reach 1,000+, so the 62 names will be repeated down the field.

In the example below, note that "Acetone" is misspelled "Aectone". The code would return an error either with "Acetone is misspelled" or all the cells with "Aectone" are highlighted.

Vinyl Chloride
etc... (to 62)
Vinyl Chloride
etc... (to 62)
Vinyl Chloride
etc... (to 62)

Highlighting the cells with errors would be a bonus.

Any help would be appreciated.



See More: Excel Valid Values Data Validation

Report •

February 3, 2010 at 13:12:46
Will every name that you need checked have a correct spelling in the valid entries list (VEL)?

For example, if the code searched for Aectone in the VEL, it won't find it and therefore it would be highlighted.

However, if it searched for Fred and didn't find it, it would also highlight it, but in reality it isn't spelt wrong, it's simply a word that isn't in the list.

My point is that we wouldn't really be checking the "spelling", we'd just be looking for words that weren't in the VEL.

Is that acceptable?

Report •

February 3, 2010 at 13:38:42
Exactly right. There are often cases where chemical names are added that shouldn't be there, so finding those names is also crucial. Sometimes chemicals have different spellings/synonyms so finding any name not on the list needs to be highlighted. Then I can make the necessary changes or deletions. "Spelling" was the wrong choice of words.

This would just be a tool to help "flag" potentially erroneous data entry, which I currently do by hand, or eye as the case may be.

Thanks for the quick response.

Report •

February 3, 2010 at 15:44:03
Perhaps you can use Conditional Formatting to highlight the offending entries.

Let's say the list you want to check is in A1:A62 and your VEL is in B1:B15.

- Select A1:A62
- Click on Format...Conditional Formatting
- Click the Condition 1 arrow and chose Formula is
- Paste this into the box next to Formula is:


- Choose your highlighting color under the Pattern tab

If the MATCH function doesn't find a value in the VEL (B1:B16) it will return an #N/A error, which will make the IF statement TRUE and the CF will be applied.

The A1 reference will change for each cell in the selected range, but the $B$1:$B$15 will hold constant.

Since you say that the list may be much longer than 62 entries, you can select the entire Column A and use this instead:


Report •

Related Solutions

February 3, 2010 at 16:09:19
re: "This would just be a tool to help "flag" potentially erroneous data entry, which I currently do by hand, or eye as the case may be."

It's probably a good idea that you automate this.

Coincidentally, on the way home from work today, I was listening to a report on the radio about a study that showed that if you are looking for something that is not supposed to be there, there's a decent chance that you won't find it when it is.

They talked about searching for things like signs of cancer on thousands of xray images or looking for guns or explosives in thousands of xrayed bags. Since the percentage of "bad" items is pretty small, and out the ordinary, there is a pretty good chance that you will miss some of them.

In one test, they put a fairly large sample of "bags with guns" in a stack of baggage images (something like 20%) and the participants missed about 7% of the guns.

They then increased the number of "clean images" so that the guns only made up about 5% of the images. The participants then missed over 30% of the guns.

The scientist who conducted the study said that you can't even talk your way into finding what you are looking for by saying "I'm going to look really, really hard and do a really good job." Even he, who knew the statistics, fell right into the middle of the pack when it came to missing the bad stuff.

Apparently the part of the brain that makes you miss things that aren't there most of the time doesn't listen to the part that says "I'm gonna find them, really I am!"

Report •

February 3, 2010 at 21:20:04
Too true about how difficult it is to find the thing your are intent upon finding. Certainly happens with the data checking I have to do. I get to a point sometimes that I completely forget what I'm looking for and have to start again...

The CF worked great! I found 7 lab chemical names in Col-A that were not identical to the names in Col-B. Then I found it difficult to locate those 7 compounds in Col-B (the problem being that the sort order for each column is not the same), so I reversed the CF to search Col-A to highlight those 7 compounds in Col-B. Worked like a charm and I'm able to correct the names to maintain data consistency.

Thanks DerbyDad, you've helped in the past and I always appreciate it.

Report •

Ask Question