Solved

Identify whether worksheet contains rows that meet more than one criteria

Posted on 2011-09-11
6
256 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)
[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 500 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 92

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 (Access MVP)
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 36520250
AgeOfEmpires,

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

0
 
LVL 92

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 (Access MVP)
ID: 36552759
Thanks,
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
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…

726 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