Multiple Column Combination Lookup And Return All Items And Get Count of Items

[excel_1317]

Example (2) (1).xlsxCan anyone help please amend your code to provide results as Column E, F, G and H in the attached file.



Code:

Sub ProcessLookUpValues()
Dim X As Long, Z As Long, Index As Long
Dim ArrLookUp As Variant, ArrIn As Variant, ArrOut As Variant, Counts As Variant
Columns("D:G").ClearContents
ArrLookUp = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
ReDim Counts(1 To UBound(ArrLookUp), 1 To 1)
ArrIn = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
ReDim ArrOut(1 To UBound(ArrIn) + UBound(ArrLookUp), 1 To 3)
For Z = 1 To UBound(ArrLookUp)
For X = 1 To UBound(ArrIn)
If UCase(ArrIn(X, 1)) Like "*" & UCase(ArrLookUp(Z, 1)) & "*" Then
Counts(Z, 1) = Counts(Z, 1) + 1
Index = Index + 1
ArrOut(Index, 1) = ArrIn(X, 1)
ArrOut(Index, 2) = ArrIn(X, 2)
ArrOut(Index, 3) = ArrLookUp(Z, 1)
End If
Next
Index = Index + 1
Next
Range("D1:G1") = Array("Count of Lookup Value", "Result 1", "Result 2", "Result 3 (Lookup Value")
Range("D2:D" & 1 + UBound(ArrLookUp)) = Counts
Range("E2:G" & UBound(ArrOut)) = ArrOut
End Sub