Hi,

I have three columns.
Column 1 has family-numbers (sorted ascending).
Column 2 has row-numbers (starting with 1 at the first instance of a new
family)
Column 3 has text.

Example:
FAM Row Text
1 1 These lines
1 2 belong to
1 3 eachother.
2 1 Do you want a
2 2 peanutbutter sandwich?
3 1 My daddy
3 2 lives in
3 3 a great big
3 4 block of flats.

As you can see there is no fixed number of texts belonging to any family.

Now I want something that merges for each family it's child-texts together,
separated by a space, and after that, delete the duplicate family rows.

Looking forward to your reply.

Try the below macro which works on the active sheet. Row 1 will have headers
and you data is in the 1st 3 columns..

Sub CombineText()
Dim lngRow As Long, lngLastRow As Long, strData As String
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = lngLastRow To 2 Step -1
strData = Trim(Range("C" & lngRow)) & " " & Trim(strData)
If Range("B" & lngRow) = 1 Then
Range("C" & lngRow) = strData: strData = ""
Else
Rows(lngRow).Delete
End If
Next
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Pluggie" wrote:

Hi,

I have three columns.
Column 1 has family-numbers (sorted ascending).
Column 2 has row-numbers (starting with 1 at the first instance of a new
family)
Column 3 has text.

Example:
FAM Row Text
1 1 These lines
1 2 belong to
1 3 eachother.
2 1 Do you want a
2 2 peanutbutter sandwich?
3 1 My daddy
3 2 lives in
3 3 a great big
3 4 block of flats.

As you can see there is no fixed number of texts belonging to any family.

Now I want something that merges for each family it's child-texts together,
separated by a space, and after that, delete the duplicate family rows.

Looking forward to your reply.