VBA Excel Match Sets of items in a range

[dj44]

Hi folks,

I am trying to match a set of items


For each fruit in the array if the fruit has 2 ids its a matching set.


Eg in this range > if Apple has both "SS" and "PP" then this is a set.



I have tried a number of things and my logic is not working.

  
  
  
  
  Sub Set_Patterns()
  
  
  Dim i As Long
  Dim d As Range
  Dim Source As Worksheet
  
  Dim fruit As Variant
  
  
  Set Source = ActiveWorkbook.Worksheets("Test_Data")
  
  
  fruit = Array("Apple", "Banana", "Plum")
  
  
  For i = 0 To UBound(fruit)
  
        For Each d In Source.Range("A2:A10")
  
  
              ' I did not know how to create another AND condition

              If d = fruit(i) And d.Offset(0, 1) = "SS" And "PP" Then
                  d.Interior.ColorIndex = 3                           'red
                  d.Offset(0, 1).Interior.ColorIndex = 3              'red
              End If
  
  
            ' Maybe an inner loop through the rows again? to identify the second ID

  
       Next d
  Next i

fruit.png

My goal is to match ONLY the sets.

A fruit must match 2 conditions - SS & PP


As you can see only Apple and Banana matched the 2 conditions - Apple has SS & PP, Banana has SS & PP.

I would like to get the names of the fruits that have double conditions met.

I will then put those into an array called matching_fruit_sets = array ("Apple", "Banana")


Do i have to loop through the rows twice?


please do take a look and thank you

[Paul_Hossler]

Maybe something like this

Option Explicit


Sub Set_Patterns()


    Dim i As Long
    Dim d As Range
    Dim Source As Worksheet
    Dim fruit As Variant
    
    Set Source = ActiveWorkbook.Worksheets("Test_Data")
    
    fruit = Array("Apple", "Banana", "Cherry")
    
    For i = LBound(fruit) To UBound(fruit)
        For Each d In Source.Range("A2:A10")
    
            ' maybe something like this <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            If d = fruit(i) Then
                If d.Offset(0, 1) = "SS" Or d.Offset(0, 1) = "PP" Then
                    d.Interior.ColorIndex = 3                           'red
                    d.Offset(0, 1).Interior.ColorIndex = 3              'red
                End If
            End If
        Next d
    Next i
End Sub

[dj44]

Hi Paul,

thank for your response.


My goal is to identify the fruit names that ONLY have both coditions met.

If a fruit has both IDS - SS & PP > thats my target set


the highlighting is just for visual.

How may I identify which fruits are a SET.

I know its a bit tricky.

I tried lots of ways.

The issue is that the fruit repeats in multiple rows but the conditions are in 1 column.

thats why its difficult.

From the image the result is Apple and Banana as these have both conditions each.

I hope I make some sense

[Paul_Hossler]

Sorry - I was only looking at your malformed AND

This might be closer to what you were asking

Option Explicit


Sub Set_Patterns()


    Dim i As Long
    Dim d As Range
    Dim Source As Worksheet
    Dim fruit As Variant
    Dim rSS As Range, rPP As Range
    
    Set Source = ActiveWorkbook.Worksheets("Test_Data")
    
    fruit = Array("Apple", "Banana", "Cherry")
    
    For i = LBound(fruit) To UBound(fruit)
        Set rSS = Nothing
        Set rPP = Nothing
    
        For Each d In Source.Range("A2:A10").Cells
            If d = fruit(i) Then
                Select Case d.Offset(0, 1)
                    Case "SS"
                        Set rSS = d
                    Case "PP"
                        Set rPP = d
                End Select
            End If
        Next d
            
        If Not (rPP Is Nothing Or rSS Is Nothing) Then
            rPP.Resize(1, 2).Interior.Color = vbRed
            rSS.Resize(1, 2).Interior.Color = vbRed
        End If
    Next i
End Sub

[dj44]

Paul,

this is absolutely phenomenal!


You are a star as always!

I did not even think of a select case.

I also did some other methods that were 3 times as long and verbose.


Thank you for your generous time and expertise in solving this.


I hope you have a fantastic weekend!