I have a workbook with over 3000 rows of data. Many of the rows have data
that needs to be concatenated into what I will call the parent cell. For
example:

A1 contains a person's name and the remainder of that row contains all the
other information related to that name.
A2 contains an additional name that needs to be concatenated with the name
in A1, seperated by a semi-colon.
A3 - A6, 7, etc may contain additional names that also need to be
concatenated with the names in A1.

I know how to write a formula to concatenate one cell's data to another, but
what I don't know how to do is to deal with a variable number of rows whose
data needs to be concatenated to a cell in the "parent" row. And since the
number of rows will vary from 2 to as many as 6, that makes the problem more
difficult - at least for me.

What I would like is a macro (or actually several macros) that, when I put
the cursor in the parent cell and press a Ctrl-?? will copy the number of
cells I need, concatenate those cells contents into the parent cell and
delete the rows that the data was in before concatenation.

Your help is much appreciated.

David

David,
If you select a range of cells, this will concatenate 2nd to
the last to first cell and then delete other cells.

HTH

Sub ConcatenateRange()
Dim rng As Range
Set rng = Selection
For i = 2 To rng.Count
rng(1) = rng(1) & ";" & rng(i)
Next i
rng(2).Resize(rng.Count - 1).EntireRow.Delete
End Sub
"David Vollmer" wrote:

I have a workbook with over 3000 rows of data. Many of the rows have data
that needs to be concatenated into what I will call the parent cell. For
example:

A1 contains a person's name and the remainder of that row contains all the
other information related to that name.
A2 contains an additional name that needs to be concatenated with the name
in A1, seperated by a semi-colon.
A3 - A6, 7, etc may contain additional names that also need to be
concatenated with the names in A1.

I know how to write a formula to concatenate one cell's data to another, but
what I don't know how to do is to deal with a variable number of rows whose
data needs to be concatenated to a cell in the "parent" row. And since the
number of rows will vary from 2 to as many as 6, that makes the problem more
difficult - at least for me.

What I would like is a macro (or actually several macros) that, when I put
the cursor in the parent cell and press a Ctrl-?? will copy the number of
cells I need, concatenate those cells contents into the parent cell and
delete the rows that the data was in before concatenation.

Your help is much appreciated.

David

Thank you, Toppers! That works great! You have just saved me a lot of work.

"Toppers" wrote:

David,
If you select a range of cells, this will concatenate 2nd to
the last to first cell and then delete other cells.

HTH

Sub ConcatenateRange()
Dim rng As Range
Set rng = Selection
For i = 2 To rng.Count
rng(1) = rng(1) & ";" & rng(i)
Next i
rng(2).Resize(rng.Count - 1).EntireRow.Delete
End Sub

"David Vollmer" wrote:

I have a workbook with over 3000 rows of data. Many of the rows have data
that needs to be concatenated into what I will call the parent cell. For
example:

A1 contains a person's name and the remainder of that row contains all the
other information related to that name.
A2 contains an additional name that needs to be concatenated with the name
in A1, seperated by a semi-colon.
A3 - A6, 7, etc may contain additional names that also need to be
concatenated with the names in A1.

I know how to write a formula to concatenate one cell's data to another, but
what I don't know how to do is to deal with a variable number of rows whose
data needs to be concatenated to a cell in the "parent" row. And since the
number of rows will vary from 2 to as many as 6, that makes the problem more
difficult - at least for me.

What I would like is a macro (or actually several macros) that, when I put
the cursor in the parent cell and press a Ctrl-?? will copy the number of
cells I need, concatenate those cells contents into the parent cell and
delete the rows that the data was in before concatenation.

Your help is much appreciated.

David