• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Identify whether worksheet contains rows that meet more than one criteria

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
Dale Fye
Asked:
Dale Fye
  • 3
  • 2
1 Solution
 
AgeOfEmpiresCommented:
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
 
Patrick MatthewsCommented:
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
 
Dale FyeAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dale FyeAuthor Commented:
AgeOfEmpires,

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

0
 
Patrick MatthewsCommented:
>>Will the items be printed out in the order they were loaded?

Good question!  Never tested it :)
0
 
Dale FyeAuthor Commented:
Thanks,
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now