[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-11
11
Medium Priority
?
800 Views
Last Modified: 2014-08-12
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
0
Comment
Question by:tyler43
  • 6
  • 5
11 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 40255235
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
 

Author Comment

by:tyler43
ID: 40255456
Hi Steve,
Attached:
test-error.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 40255605
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:tyler43
ID: 40255810
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
 
LVL 24

Expert Comment

by:Steve
ID: 40255922
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
 

Author Comment

by:tyler43
ID: 40255940
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
 
LVL 24

Expert Comment

by:Steve
ID: 40255998
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
 

Author Comment

by:tyler43
ID: 40256072
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 40256402
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
 

Author Closing Comment

by:tyler43
ID: 40256447
simply excellent!
0
 

Author Comment

by:tyler43
ID: 40256462
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question