Multiple rows returned from a vlookup
Nick,
I have an excel table with 2 columns - client & contact. There are 0..n contacts per client. I want to do a vlookup and get all the contact names (comma separated). How can I do that?
Thx
Laks
ExcelExperts.comExcel Consultancy, VBA Consultancy, Training and Tips Call:+442081234832 |
|
Excel / VBA ConsultancyFree Training VideosFree SpreadsheetsExcel / VBA JobsNavigationWho's onlineThere are currently 0 users and 472 guests online.
New Excel Experts
Current Excel / VBA Jobs |
Multiple rows returned from a vlookupNick, I have an excel table with 2 columns - client & contact. There are 0..n contacts per client. I want to do a vlookup and get all the contact names (comma separated). How can I do that? Thx |
Highest Ranked Users
Recent Blogs
ForumsRecent comments
User login |
Laks - why not use a pivot
Laks - why not use a pivot table to get the required names, then unfortunately, you need some VBA to concatenate the names to separate by commas and produce the string (which I presume ur using to send mail with)
Easier to do with VBA than
Easier to do with VBA than worksheet functions ..
Assuming that
a) You have a list of unique contact names in range G1:G5
b) Your Clients are listed in range B11:B30
c) The client contacts are listed in C11:C30
Then this code will compile the string for you ..
Private Sub CommandButton1_Click()
Dim iY As Integer, iY1 As Integer, sContact As String, SLineOut As String
For iY = 1 To 5 'Iterate through all contacts
sContact = Range("G" & iY).Value 'Assigns the reference contact name
SLineOut = "" 'Reset the output string for each new reference contact
For iY1 = 11 To 30 'Iterate through all clients
If Range("B" & iY1).Value = sContact Then ' current client contact = reference contact
SLineOut = SLineOut & Range("A" & iY1).Value & ";" 'Add to output string if true
End If
Next iY1
SLineOut = Left(SLineOut, Len(SLineOut) - 1) 'Remove final semicolon
Range("J" & iY).Value = SLineOut
Next iY
End Sub
Numbers and cell references need to be amended to suit
VBA works
Thanks Nick and Andy. I took the VBA route.