Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Identify whether worksheet contains rows that meet more than one criteria

Posted on 2011-09-11
6
Medium Priority
?
259 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
Independent Software Vendors: 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

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.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

618 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