Columns to rows (table) 2

[Steven811]

Hi

I have a column containing 1200 records that I want to convert into a table in Excel so that I can clean it up and export it into Access.

The data looks like this:

A1:Name
A2:Address1
A3:Address2
to A10: sometimes the last 2 0r 3 fields are empty

etc

and I want it to become:
A1:Name B1:Address C1:Address2 etc

I have found references to transpose and offset, both have been explored, unsuccessfully.

Could someone point me in the right direction as I've wasted hours on what I thought would be a simple issue.

Thanks

Steven811

 

[SkipVought]

Steven811,

Is it ALWAYS 10 rows per record?


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[DaleWatson123321]

Based on your data being consistent blocks of 10 rows, the following routine will transpose the data onto Sheet2, with each 10 rows becoming 1 record.

Sub Transpose_Data()
Application.ScreenUpdating = False
lastrow = [A65536].End(xlUp).Offset(0, 5).Row
[a1].Select
c = 1
r = 1
Do While ActiveCell.Row <= lastrow
For i = 1 To 10
ActiveCell.Copy _
Destination:=Worksheets("Sheet2").Cells(r, c)
ActiveCell.Offset(1, 0).Activate
c = c + 1
Next i
c = 1
r = r + 1
Loop
[a1].Select
Application.ScreenUpdating = True
End Sub

Regards, Dale Watson

 

[KenWright]

Assuming your data exactly as stated, being in ColA starting row 1, and result needed being rows 1-10 transposed, rows 11-20 etc etc. In cell B1 put the following:-

=OFFSET($A$1,ROW()*10-(12-COLUMN(B1)),0)

Copy cell and paste to B1:K120

Copy all data and paste special as values and then delete Col A.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Steven811]

Thanks for your help gents.

I opted for Ken's solution as I thought it would be easier and it works a treat.

V Grateful.