Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0

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

    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:

    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
    Last edited by excel_1317; 06-26-2013 at 10:29 AM. Reason: Code tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I am assuming columns A to D will be filled already. And you need to populate columns E to H. Correct?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    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.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    And how come the count for
    FOLEY
    CARLSON
    Francis
    are only
    1
    1
    2
    I think it should be
    4
    2
    3
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you respond to the above query?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    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. Custom Filter.jpg

  8. #8
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0
    Somebody, please help me....

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    How is it 3,2,2? There are four cells that has FOLEY in it.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    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

Similar Threads

  1. Multiple Value Return Using Lookup
    By excel_1317 in forum Excel Help
    Replies: 1
    Last Post: 06-25-2013, 01:06 PM
  2. Replies: 4
    Last Post: 04-24-2013, 10:04 AM
  3. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Lookup Multi-Column For Unique String Combination For Numeric Value Output
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-03-2011, 07:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •