Checking if items in a table exist/match ones on a different sheet with Excel VBA.

I have a large table with data and need several columns checked for unmatched text items (items repeat) against another sheet where items are listed in various ranges and don't repeat (kind of like a set up sheet for a user). The idea is to catch data errors in the table such as misspelling of items, extra spaces and etc. Blank cells however are allowed. How one would iterate through a table column checking if each item matches either of the items on another sheet's given range and if it doesn't match have that have that cell in the data table activated and highlighted to draw user attention? For illustration purposes let's say table is "Table1" with column than needs checking "Column1"  on Sheet2, and the range I am checking it against is in Sheet1 in $A$1:$A$10.

thx in advance
tyler43Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Tyler, could you post a very simple example workbook.
Being able to see some example of the structure would really help get to an answer as fast as possible.

Thanks,
Steve.
0
tyler43Author Commented:
Hi Steve,
Attached:
test-error.xlsm
0
SteveCommented:
Have you considered Conditional formatting:

Using a formula to determine TRUE...
=AND(COUNTIF(Sheet1!$A$2:$A$11,C3)=0,C3<>"")

This will highlight as desired.
See attached.

ATB
Steve.
U--test-error.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tyler43Author Commented:
Steve,
It's an interesting solution that works if the table is static. In my case the user can clear the data in a table with the VBA code I built, and then paste a different set of data (of a different size). In that case conditional formatting breaks.
0
SteveCommented:
The range for the data can be dynamic by using OFFSET:
Something like:
=AND(COUNTIF(OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A),0),C3)=0,C3<>"")

or just use the whole column:
=AND(COUNTIF(Sheet1!$A:$A,C3)=0,C3<>"")

The conditional formatting can also be set to the whole column (or sheet).
This will make it more static.

If you put the formula =AND(COUNTIF(Sheet1!$A:$A,C3)=0,C3<>"") in D3 and copy it down it will do TRUE/FALSE for bad/good lines.

Is there a preference for the method of output you would like?
We can prety much do anything you want, if you can describe the result you would like.

ATB
Steve.
0
tyler43Author Commented:
What I meant is that the Table1 on Sheet2 can be cleared, and change sizes depending on data pasted by user. Area on Sheet1 is static.
0
SteveCommented:
Indeed, so the Range for Conditional formatting can be expanded to the whole of column C:C
This will mean that any cut and paste should not effect the conditional formatting.

The attached workbook will not matter if  the table grows or shrinks, it should still maintain the formating.

If you want it to highlight using VBA this is possible too.
Up to you.
U--test-error.xlsm
0
tyler43Author Commented:
I think the only drawback of this is that if there is a title above the table or the header of the column is something different from Sheet1 then these areas will also be highlighted as erroneous. Anyways to offset these areas?
0
SteveCommented:
Conditional formatting will not allow for the use of OFFSET it always replaces with the actual range.
Even naming the range converts it back to a standard range.

You can modify the Conditional Formatting one step further to exclude the title row:
=AND(COUNTIF(Sheet1!$A:$A,C1)=0,C1<>"",ROW(C1)>2)
U--test-error.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tyler43Author Commented:
simply excellent!
0
tyler43Author Commented:
Steve I have a follow up question but don't want to piggy back on this one since I already accepted the solution, and it works. Ill just post the question in general, I think it would be easy for you to answer. In essence I would also like to have a counter of unmatched items.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.