Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Vlookup with multiple occurances of a value

  1. #11
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    It is a list of postcodes, so yes, I understand the problem. Could the extra results go at the bottom of the list, or in the next column perhaps?

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Ok, how about
    Code:
    Sub Hyp40()
       Dim Cl As Range
       Dim Ws1 As Worksheet, Ws2 As Worksheet
       Dim Sp As Variant
       Dim i As Long
       
       Set Ws1 = Sheets("Master")
       Set Ws2 = Sheets("Sheet1")
       With CreateObject("Scripting.dictionary")
          For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
             Sp = Split(Cl.Value, ",")
             For i = 0 To UBound(Sp)
                If Not .Exists(Trim(Sp(i))) Then
                   .Add Sp(i), Cl.Offset(, 1).Resize(, 3)
                Else
                   Set .Item(Trim(Sp(i))) = Union(.Item(Trim(Sp(i))), Cl.Offset(, 1).Resize(, 3))
                End If
             Next i
          Next Cl
          For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
             If .Exists(Cl.Value) Then
                Ws2.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(.Item(Cl.Value).Count / 3, 1).Value = Cl.Value
                .Item(Cl.Value).Copy Ws2.Range("C" & Rows.Count).End(xlUp).Offset(1)
             End If
          Next Cl
       End With
    End Sub
    Change sheet names to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Wow thank you Fluff, that certainly solves the problem. Amazing.
    I have been struggling for days with this. Thank you so much.
    Now to see if I can work out what the code is doing!
    Last edited by Hyp40; Sep 21st, 2019 at 12:05 PM.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    It already does that
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Sorry Fluff - I was so excited that I didn't check the output properly. It does it all! I amended my reply - but not soon enough.
    Thank you

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Hi Fluff, sorry to trouble you again.
    Is there a maximum number of rows I can search on? This works perfectly on my sample data but when I try it on the actual data, I get "Run-time error '457': This key is already associated with an element of this collection". This seems to happen if I have more than 1450 rows in the Master List.
    My Master list has 66000 rows and the look up list has around 5000 rows.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    There was a slight flaw in the code, try
    Code:
    Sub Hyp40()
       Dim Cl As Range
       Dim Ws1 As Worksheet, Ws2 As Worksheet
       Dim Sp As Variant
       Dim i As Long
       
       Set Ws1 = Sheets("RC")
       Set Ws2 = Sheets("Form")
       With CreateObject("Scripting.dictionary")
          For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
             Sp = Split(Cl.Value, ",")
             For i = 0 To UBound(Sp)
                If Not .Exists(Trim(Sp(i))) Then
                   .Add Trim(Sp(i)), Cl.Offset(, 1).Resize(, 3)
                Else
                   Set .Item(Trim(Sp(i))) = Union(.Item(Trim(Sp(i))), Cl.Offset(, 1).Resize(, 3))
                End If
             Next i
          Next Cl
          For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
             If .Exists(Cl.Value) Then
                Ws2.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(.Item(Cl.Value).Count / 3, 1).Value = Cl.Value
                .Item(Cl.Value).Copy Ws2.Range("C" & Rows.Count).End(xlUp).Offset(1)
             End If
          Next Cl
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Thank you Fluff. That has worked perfectly. Can't say that I understand the slight difference but you have made my weekend. I'm really grateful

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    If you have data like
    PE14 8PS, PE12 0JW
    This line
    Code:
    If Not .Exists(Trim(Sp(i))) Then
    will trim the value so it will check for "PE14 8PS" and "PE12 0JW", if the 2nd value didn't exist it would add " PE12 0JW" (note the space at the start) rather than "PE12 0JW"
    So the mod I made corrects that problem.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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