combining cells' data into one cell

[Eric]

In my spreadsheet I have several instances of a title
followed by related cells (maybe 1 , 2 or more) of data. I
want to take the cells of data and put them into the cell
immediately to the right of this title.

e.g. Cell "A10" contains "Title".
Cell "A11" contains "text 1"
Cell "A12" contains "text 2"
Cell "A13" contains "text 3"

I want cell "B10" to contain "text 1 text 2 text 3".
Cell "A10" remains unchanged. Cells A11, A12 and A13 are
to be deleted.

How do i do this?

 

[Nigel]

One question before this can be answered........
Since you say that there can be a varying number of entries following a
title, how do you determine which is a title and which is not?

Cheers
Nigel

 

[Patrick Molloy]

Sub ReArrangeTable()

Range("A1").CurrentRegion.Sort Key1:=Range("A1"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim lastrow As Long
Dim cellvalue As String

lastrow = Range("A65000").End(xlUp).Row

For lastrow = lastrow To 2 Step -1

If Cells(lastrow - 1, 1) = Cells(lastrow, 1) Then

Range(Cells(lastrow, 2), _
Cells(lastrow, 200).End(xlToLeft)).Copy

Cells(lastrow - 1, 200).End(xlToLeft).Offset
(0, 1).PasteSpecial xlValue

Rows(lastrow).Delete
End If
Next
End Sub