A better way to find when looking for two matching criteria

cochese · Nov 10th 2012

By better, I mean more efficient. The below code works, but I can't help but feeling that it's sloppy and there is a better way to do it.

The goal is to match up two sets of data; however, each set of data has two variables to the match. Data Set 1 consists of a column of store numbers, a column of employee IDs, and then a column of results. Data Set 2 consists of a store number column and employee ID column. An employee ID can be used in multiple stores - that is employee ID '123' can exist in each store. Therefore, when I search Data Set 2 I have to make sure what is found is for the right store. In other words, a search from Data Set 1 for employee ID '123' (in store ABC) may find employee ID '123' in Data Set 2 but for an employee in store DEF, in which case findnext has to be used to search for the next employee ID '123' until a match of ID and store are found.

Code

Sub AssignBrackets()


Dim rngBracketIDs As Range
Dim rngCashierIDs As Range
Dim rngID As Range
Dim rngMatch As Range
Dim str1stFind As String


    Set rngBracketIDs = Selection 'Data Set 1 employee IDs
    
    Set rngCashierIDs = Selection 'Data Set 2 employee IDs
    
    For Each rngID In rngBracketIDs
        
        'search for an ID from Data Set 1 in Data Set 2
        Set rngMatch = Selection.Find(What:=rngID, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
        'if no employee ID match is found move on
        If Not rngMatch Is Nothing Then
            'if the store number from Data Set 1 doesn't match the find in Data Set 2 then they aren't a match
            'there are identical employee IDs in stores, which is why a find has to be further tested to make sure it's from the same store
            If rngID.Offset(0, -1) <> rngMatch.Offset(0, -1) Then
            
                str1stFind = rngMatch.Address


                'in this loop the code keeps loop until it finds an employee ID that matches the same store or until the search starts back
                'at the beginning                        
                Do
                
                    Set rngMatch = Selection.FindNext(After:=rngMatch)
                
                Loop Until rngID.Offset(0, -1) = rngMatch.Offset(0, -1) Or _
                str1stFind = rngMatch.Address
            
            End If
            
            'here I test if the first find or the find from the DO LOOP match, because if they don't
            '(meaning the DO LOOP circled around to the beginning) I want to highlight the data
            'from Data Set 1
            If rngID.Offset(0, -1) = rngMatch.Offset(0, -1) Then
            
                rngMatch.Offset(0, 1) = rngID.Offset(0, 2)
            
            Else
                'the employee ID was found, but no matching store was
                rngID.Interior.ColorIndex = 6
                
            End If
        
        Else
        
            'the bracket id is not found in any store
            rngID.Interior.ColorIndex = 3
        
        End If
        
    Next


End Sub

Display More

Thank you.

dangle · Nov 10th 2012

Re: A better way to find when looking for two matching criteria

Hi Cochese

It may be that the using the 'Dictionary' object is a better way of doing this but it would help to see the actual data and desired results.

cochese · Nov 12th 2012

Re: A better way to find when looking for two matching criteria

Quote from dangle;636012

Hi Cochese

It may be that the using the 'Dictionary' object is a better way of doing this but it would help to see the actual data and desired results.

I had never heard of the Dictionary object, but I Googled it and I'm very glad I now know about it; however, I'm not sure it solves my problem efficiently. I'm attaching a sample data spreadsheet. Thank you for the help.

**mikerickson** · Nov 12th 2012

Re: A better way to find when looking for two matching criteria

Could you create a helper column for each data set. Helper = Employee ID & Store ID and then search through that.

Or you could use Auto-Filter to isolate those rows that matched a given employee/store combo.

Or you could sort each data set on Employee and then on Store to make the searching quicker.

With someRange sorted, you know that every cell in Range(FirstCell, LastCell) contains searchTerm

Code

With someRange
    Set FirstCell = .Find(searchTerm, After:= .Item(.Cells.Count), SearchDirection:=xlNext)
    Set LastCell = .Find(searchTerm, After:=.Cells(1,1), SearchDirection:=xlPrevious)
End With

cochese · Nov 12th 2012

Re: A better way to find when looking for two matching criteria

I could work with sorting, but I just don't know if it would be a more efficient means than what I already created (and have working). I really like your idea of combining the two columns and then searching. I think that might just be the solution!

A better way to find when looking for two matching criteria

Participate now!

Share