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







30592

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!

:)