?
Solved

Identify whether worksheet contains rows that meet more than one criteria

Posted on 2011-09-11
6
Medium Priority
?
257 Views
Last Modified: 2012-06-21
Sheet #1 contains about 500 rows with column C defined as Group (numeric) and column D defined as Sub-Group (numeric), There are only about 75 unique combinations of Group/Sub-Group.

As part of a much larger process, the first step is to identify which combinations of Group/Sub-Group exist in Sheet #1 which don't exist in Sheet #2, which also contains Group (column A) and sub-group (Column B).  

My intent is to loop through the rows of Sheet #1 and confirm whether there is a matching Group/Sub-group in Sheet #2.  If not, I'll add that Group/sub combination to a message and after checking all of the rows, present a message box for my user.

The looping works just fine, I just have not been able to figure out how to determine whether a row matches more than one criteria.  I know I could loop through each row of Sheet #2 and test both cells agains the cells from Sheet #1, but I'm looking for a more elegant method.
0
Comment
Question by:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
AgeOfEmpires earned 2000 total points
ID: 36519735
I count this as more elegant, but you may/may not.  I essentially use two dictionaries (you need to add a reference to the Microsoft Scripting Runtime library), and let VBA do the "heavy lifting" to loop through list 2, via the exists method of the dictionary, to determine what is in list 1 but not in list 2.  

There are a few caveats here.  I've done no error checking to make sure that each and every value in both columns on both sheets do indeed contain proper keys.  I also depend upon the first blank cell in column A (on each sheet) to indicate the end of the list.  

I simply output keys from list 1 that don't exist in list 2 to the immediate window via debug.print.  You'd add any processing you needed here.  Also, the BuildKey function can be changed to meet your needs.

Essentially, I store each unique key in d1 (for sheet1) and d2 (for sheet2) and then check each item in d1 to see if it exists in d2.  If not, I output it.

See what you think.

Sub test()
  Dim ptr As Range
  Dim d1 As New Dictionary, d2 As New Dictionary
  Dim a As Variant

  Set ptr = Worksheets("Sheet1").Range("A1")
  FillDictionary ptr, d1
  Set ptr = Worksheets("Sheet2").Range("A1")
  FillDictionary ptr, d2
  
  For Each a In d1.Keys()
    If Not d2.Exists(a) Then
      Debug.Print a
    End If
  Next a
  
  Set d1 = Nothing
  Set d2 = Nothing
  
End Sub

Sub FillDictionary(ptr As Range, d As Dictionary)
  Dim row As Long
  Dim key As String, s As String
  
  row = 0
  s = Trim(ptr.Offset(row, 0).Value)
  While s <> ""
    key = BuildKey(s, Trim(ptr.Offset(row, 1).Value))
    
    If Not d.Exists(key) Then
      d.Add key, ""
    End If
    
    row = row + 1
    s = Trim(ptr.Offset(row, 0).Value)
  Wend
  
End Sub

Function BuildKey(s1 As String, s2 As String)
  BuildKey = s1 & "*" & s2
End Function

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36519916
I am a big fan of the dictionary.  In this case I would be tempted to use a "dictionary of dictionaries" approach, as documented in my article:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
0
 
LVL 48

Author Comment

by:Dale Fye
ID: 36520129
Patrick,

Great article, but what I didn't see mentioned is anything about the dictionary order.  If I load a dictionary in a particular order and then use:

For each x in MyDictionary
    debug.print MyDictionary.item(x)
next

Will the items be printed out in the order they were loaded?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Author Comment

by:Dale Fye
ID: 36520250
AgeOfEmpires,

Will give this a shot and see how I like it.

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36520717
>>Will the items be printed out in the order they were loaded?

Good question!  Never tested it :)
0
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 36552759
Thanks,
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

765 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