Transpose Varying Number of Rows to Table

[TheBaron26]

Hi,

I'm looking to take an input of address data and transpose it into a table like structure. The input would look something like this (cells A1:B17):

Description, Postcode,
Joe Bloggs,AB1 2CD,
ABC House,,
ABC Road,,
ABC Town,,
ABC City,,
Jon Doe, EF3 4GH,
DEF House.,
DEF Road,,
DEF Town,,
DEF City,,
DEF County,,
DarthVader,IJ5 6KL,
GHI House,,
GHI Road,,
GHI Town,,
GHI County,,

And I'd like to work down the data set and transpose the correct number of address lines into a table format, but the number of address lines will vary between 4 and 5. I'd hope the output would look something like (cells A1:G4):

Description,Postcode,Address1,Address2,Address3,Address4,Address5,
Joe Bloggs, AB1 2CD, ABC House, ABC Road, ABC Town, ABC City,,
Jon Doe, EF3 4GH, DEF House, DEF Road, DEF Town, DEF City, DEF County,
DarthVader, IJ5 6KL, GHI House, GHI Road, GHI Town, GHI County,,

Any help would be much appreciated!

R

 

[venkat1926]

the data like this

Description Postcode
Joe Bloggs AB1 2CD
ABC House
ABC Road
ABC Town
ABC City
Jon Doe EF3 4GH
DEF House.
DEF Road
DEF Town
DEF City
DEF County
DarthVader IJ5 6KL
GHI House
GHI Road
GHI Town
GHI County


copy this data(in sheet 1) to sheet2 from A1
ASSUME LESS THAN 3000 ROWS.

TRY the macro "test" (macro "undo" is to undo the result of th e macro for retesting

Sub test()
Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
Dim lastcell As Range
Set lastcell = Range("A1").End(xlDown)
Set r1 = Range("B2")
Do
Set r2 = r1.End(xlDown)
'MsgBox r2.Address
If r2.Row > 3000 Then
Set r3 = r1.Offset(1, -1)
Set r4 = Cells(Rows.Count, "A").End(xlUp)
GoTo nextstep
End If

Set r3 = r1.Offset(1, -1)
Set r4 = r2.Offset(-1, -1)
nextstep:
Range(r3, r4).Copy
r1.Offset(0, 1).PasteSpecial , Transpose:=True
Range(r3, r4).EntireRow.Delete

Set r1 = r2
If r1.Row > 3000 Then Exit Sub
Loop
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")

End Sub

 

[TheBaron26]

Hi venkat1926,

This works perfectly, and also adjusts to a varying number of address lines, so thank you - really appreciated!

Regards

R