VLOOKUP and multiple rows ?

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have two worksheets:
#1 contains client numbers and data.
client no. data
1 xyz
2 abc
3 def
4 wed

#2 contains client numbers and the employees attached to
the client.
client no employee
1 joe
1 tom
2 jim
3 ann

**Here's my problem! Is there a way to do a lookup on
spreadsheet # 1 to bring in the employee data from
spreadsheet #2 (a normal VLOOKUP) and if there are mutiple
employees to either concatenate the data->

client no employee
1 joe,tom


or put it into a new column -->
client no employee employee
1 joe tom


I have tens of thousands of data items and this is
becoming a major issue.
Thanks for you help!

-Joe

..
 
try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
mystr = ""
With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & c.Offset(, 1) & " "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
'MsgBox mystr
cel.Offset(, 2) = mystr
End With
Next cel
End Sub
 
Back
Top