Excel data validation macro

[TessCoes]

Any Excel experts out there?
I've been asked to look at the feasibility of running a macro in Excel that will check entered (mostly cut/pasted) data against a table of valid values.
I've googled 'data validation' etc but this seems to mostly bring back references to drop down lists as data is manually entered into a cell.
The data to be check could be upto 31 columns wide, by any number of lines( 000's or 0000's maybe). I have seen something similar in SQL statements, where a piece of data can be checked to see if it exists in another table....is there something like this available in Excel VB?
Any advice gratefully received! :ugh:

[bnt]

You don't say what kind of data it is, but if it's simple string or numeric data, a Macro is overkill, in my opinion.

If the data is in Table form, and can be viewed as a list of 1-line "Records", then you have some good tools at your disposal. Just sorting the table on the relevant column can send all "out of range" values to the top and/or bottom, where you can easily see them. There's also the "Data Filter" menu option , where you can show or hide records by the values in columns, even "show only blanks" to highlight missing data.

For more unstructured data, you have the Conditional Formatting feature. Data matching the rule can be made to appear in a larger font, in red, etc., so you can spot it immediately. If it's a lot of data, view it at 10% Zoom. The good values are just dots, but you can make any invalid values stand out like the proverbial sore thumb.

[bnt]

Lastly: it sounds like you have a list of "valid" data in a table. In that case, you may want to investigate the COUNTIF functions. I imagine a scenario like this:
- you have the pasted data on Sheet 1 of the Workbook
- you have the table of valid data on Sheet 2, cells A1..A10)
- On Sheet 3, set up the first test formula "under" the data on Sheet 1. (If the first value is in Cell A1 of Sheet 1 (Sheet1!A1), then put the test formula in Sheet3!A1.

The formula would do a "count" of the valid values, from the table, so a result of Zero means that the data is not in the table (invalid). I just did a quick test: with valid data in Sheet2 cells A1..A10, the formula in Sheet3!A1 is =COUNTIF(Sheet2!$A$1:$A$10,Sheet1!A1)

Note the $ signs are necessary to "lock" the references to the fixed table of valid data. This means that you can copy the formula to match the size of the table in Sheet 1: Sheet1!A1 becomes Sheet1!A2 and so on, but the valid data table stays in one place.

[TessCoes]

Thanks bnt, sadly it's a lot more complicated.
Data in each column would be either numbers or text...sometimes both!
To clarify...any given cell in this spreadsheet may be populated with numbers (or text, or both!!), but not just any number or text...they have to be valid when compared to a list of values....simple ranges won't work ie, we may use 102000 & 103000 but nothing between, (or indeed ABC & XYZ but not STU).
There's too many valid values and cells to provide drop downs for each one....and in most cases the data would pasted in, not entered manually.
What I have is a file that contains the defined valid values available for each column in the s/sheet....can I get excel to compare the values that are entered/pasted in the s/sheet against this file somehow...similar to v/h lookups...but in the background...via macro?
I may be asking the impossible I guess!

bnt, just seen your 13:02...sounds interesting...will have a play

[bnt]

If you can do it without Macros, then all the better, I think. I use Macros myself, but it is basically programming, with all that implies: if you're not careful, a Macro can misbehave, overwrite things unexpectedly. It needs "care and feeding", you might say. :hmm: