How about a small macro?
Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range
Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
'Add a dummy row at the bottom to mark end of data
With .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
.Value = "xxxx"
.Offset(0, -1).Value = "XXXX"
End With
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set TopCell = .Cells(FirstRow, "B")
Set BotCell = .Cells(FirstRow, "B")
For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Value <> "" Then
Set BotCell = .Cells(iRow - 1, "B")
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
Set TopCell = .Cells(iRow, "B")
Set BotCell = .Cells(iRow, "B")
End If
Next iRow
On Error Resume Next
'clean up "blank cells and temporary bottom cell, too!
.Range("C:C").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.Range("b:B").Delete
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm