Example (2).xlsxRick, please amend your code to provide results as Column E, F, G and H in the attached file.
Code:
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
Reply With Quote
Originally Posted by excel_1317
Bookmarks