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

Attachment 907Can anyone help 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

---

excel_1317, I thought it'd be better that Rick looks at it, but I'll give it a shot. I saw the columns that you want, but can you explain what exactly they are, or what is the logic.

I am assuming columns A to D will be filled already. And you need to populate columns E to H. Correct?

Quote:

---

Originally Posted by Excel Fox

I am assuming columns A to D will be filled already. And you need to populate columns E to H. Correct?

---

Thank you for looking into this. You got it right...

Column A has company names and column B has IDs. Column C and Column D contains Lookup Values which i need to lookup in Column B.

Rick's code needs to be amended so that it takes columns C and D. Presently the code takes values from ONLY from Column C and lookup in Column A and populate columns E to H.

And how come the count for

FOLEY

CARLSON

Francis

are only

1

1

2

I think it should be

4

2

3

Can you respond to the above query?

Quote:

---

Originally Posted by Excel Fox

And how come the count for

FOLEY

CARLSON

Francis

are only

1

1

2

I think it should be

4

2

3

---

My mistake, the count should be as....

Foley - 3
CARLSON- 2
Francis - 2

The logic is to make the code work like a custom auto filter. For eg. Please have a look at attached screenshot. Here i have applied custom autofilter to column A. Attachment 906

Somebody, please help me....

How is it 3,2,2? There are four cells that has FOLEY in it.

Quote:

---

Originally Posted by Excel Fox

How is it 3,2,2? There are four cells that has FOLEY in it.

---

Please refer to my screenshot in previous post. I want the macro to function like custom autofilter of excel. So folry+hoag occurs3 times in column A

OK, it's 'AND', not 'OR'. Fair enough.

Now why is Francis Bozicevic 2? Shouldn't it be 3?

Quote:

---

Originally Posted by Excel Fox

Now why is Francis Bozicevic 2? Shouldn't it be 3?

---

Yes, you are right.... it should be 3

Try this

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("E:H").ClearContents
ArrLookUp = Range("C2:D" & Application.Max(Cells(Rows.Count, "C").End(xlUp).Row, Cells(Rows.Count, "D").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)) & "*" And UCase(ArrIn(X, 1)) Like "*" & UCase(ArrLookUp(Z, 2)) & "*" 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) = Trim(ArrLookUp(Z, 1) & " " & ArrLookUp(Z, 2))
End If
Next
Index = Index + 1
Next
Range("E1:H1") = Array("Count of Lookup Value", "Result 1", "Result 2", "Result 3 (Lookup Value)")
Range("E2:E" & 1 + UBound(ArrLookUp)) = Counts
Range("F2:H" & UBound(ArrOut)) = ArrOut
End Sub

---

Quote:

---

Originally Posted by Excel Fox

Try this

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("E:H").ClearContents
ArrLookUp = Range("C2:D" & Application.Max(Cells(Rows.Count, "C").End(xlUp).Row, Cells(Rows.Count, "D").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)) & "*" And UCase(ArrIn(X, 1)) Like "*" & UCase(ArrLookUp(Z, 2)) & "*" 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) = Trim(ArrLookUp(Z, 1) & " " & ArrLookUp(Z, 2))
End If
Next
Index = Index + 1
Next
Range("E1:H1") = Array("Count of Lookup Value", "Result 1", "Result 2", "Result 3 (Lookup Value)")
Range("E2:E" & 1 + UBound(ArrLookUp)) = Counts
Range("F2:H" & UBound(ArrOut)) = ArrOut
End Sub

---

---

Working like a charm...!!! Thanks