Concatenating all records in column b where column a=x
 

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

Concatenating all records in column b where column a=x
 

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

Concatenating all records in column b where column a=x
 

Hi Sheloo:

Thanks for your reply- I don't know a lot about how to write VB Code though-

How would I modify that so that it becomes a function that I can call from a
cell where I would send bldgname as a value from another cell and the column
containing the buildingids as a range to be evaluated and the column
containing the inhabitants as the range from which to concatenate?



"Sheeloo" wrote:

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