Re-organising data in excel

Firstly hi to all - new to forums but hoping someone can help.

I have been running some data exports from a booking system and have come across an annoying issue with addresses.

Although all other entries are great with one row per transaction (unique booking number per entry) the address details have come out like below:

The booking number and first line of address on one row - then a number of rows below for the rest of the address.

I need this is the format of rows to columns - been trying the transpose for copy and paste but no luck.

Any help would be appreciated.


539-00000000001 Line1
Line2
Line3
PCODE
539-00000000002 ALine1
ALine2
ALine3
ALine4
APODE
539-00000000003 L1
L2
L3
L4
L$PC

Hi Andy

Thanks for the reply.

After some late searching on the internet last night - I actually stumbled across a script on another forum that seems to do the job!

Code below.

Sub Macro1()

Dim lngRowLast As Long, _
lngRowPaste As Long, _
lngColOffset As Long
Dim rngCell As Range, _
rngDataSet As Range
Dim varDelimiter As Variant
Dim strSourceTab As String, _
strOutputTab As String

varDelimiter = "-"
'Tab name containing source data. Change to suit.
strSourceTab = "Sheet1"
'Tab name for data output. Change to suit.
strOutputTab = "Sheet2"

lngRowLast = Sheets(strSourceTab).Cells(Rows.Count, "A").End(xlUp).Row

'Assumes the original dataset is in Column A and starts at Row 1. Change to suit.
Set rngDataSet = Sheets(strSourceTab).Range("A1:A" & lngRowLast)

Application.ScreenUpdating = False

For Each rngCell In rngDataSet

If Left(rngCell.Value, 1) = varDelimiter Then
If lngRowPaste = 0 And lngColOffset = 0 Then
lngRowPaste = 1
lngColOffset = 1
Else
lngRowPaste = lngRowPaste + 1
lngColOffset = 1
End If
ElseIf lngRowPaste = 0 And lngColOffset = 0 Then
lngRowPaste = 1
lngColOffset = 1
End If

Sheets(strOutputTab).Cells(lngRowPaste, lngColOffset).Value = rngCell.Value
lngColOffset = lngColOffset + 1

Next rngCell

Application.ScreenUpdating = True

End Sub