# Thread: Vlookup with multiple occurances of a value Thanks:  4 Post #5345351 (1)Post #5345496 (1)Post #5345559 (1)Post #5345339 (1) Likes:  2 Post #5345496 (1)Post #5345559 (1)

1. ## 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. ## Re: Vlookup with multiple occurances of a value

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

3. ## 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!

4. ## Re: Vlookup with multiple occurances of a value

It already does that

5. ## 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. ## Re: Vlookup with multiple occurances of a value

Glad to help & thanks for the feedback

7. ## 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. ## 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```

9. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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