CONCATENATE

[lehigh]

Hi All

This is best suited for a data base, but....................


Sheet "A" has a list of companies

Sheet "B" has a list of names of people who work for the companies on
sheet "A"
In most cases there is more than one person who works for any one
company

Is there a formula I could write into a cell on sheet "A" which will
lookup sheet "B" and concatenate all the people who work for that
company into that one cell on sheet "A"?


Thanks for any help you can give me.

Tom Snyder

 

[Lance]

You could do it with a user defined funtion.
The function below usage
lu(company name,company name employee range)

this function assumes the "company name employee" range is
in adjoining columns with company first then employee name.


Function lu(st As String, r As Range) As String
Dim c As Range
Dim s As String
For Each c In r
If c.Value = st Then
s = s + "," + c.Offset(0, 1)
End If
Next c
lu = s
End Function