Here is a macro... Can you adapt to a UDF meeting your requirements?

This assumes that your data is Col A & B in Sheet1..
If you enter a buildingid in cell B1 of Sheet2 then this will write
the inhabitants in B2

Sub copyMacro()
Dim lastRow1, lastRow2 As Long
Dim lastCol As Long
Dim i, j, k, startRow As Long
Dim bldgName, inhabitant As String
Dim timeSlot(255) As String

With Worksheets("Sheet1")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

bldgName = Worksheets("Sheet2").Range("B1").Value

For i = 2 To lastRow1
If Worksheets("Sheet1").Cells(i, 1) = bldgName Then
inhabitant = inhabitant & Worksheets("Sheet1").Cells(i, 2) & " "
End If
Next i

Worksheets("Sheet2").Cells(2, 2) = inhabitant

End Sub
"steve-o" wrote:

HI :

I have 3 columns of cells:

BUILDINGID INHABITANTS
123123 joe
123123 sam
123123 tim
333444 mary
333444 louise
333444 tom
222333 marsha

I would like to have a table that has each buildingid only once as a unique
record with a second field having a string of the concatenated inhabitants.
For instance:

buildingid inhabitants
123123 joe sam tim
333444 mary louise tom
222333 marsha

How can I create a function that if I give it a chosen buildingid, the name
of a range of building ids, and the name of a range of inhabitants, will
give me a single record with a concatenated string of all inhabitants that
are in records with the chosen building id?

Thanks in advance for your help,

Steve