Solved

Identify whether worksheet contains rows that meet more than one criteria

Posted on 2011-09-11
6
253 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 (Access MVP)
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
AgeOfEmpires earned 500 total points
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
AgeOfEmpires,

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

0
 
LVL 92

Expert Comment

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

Good question!  Never tested it :)
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks,
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now