Convert Columns into Rows

[merryperson]

I have a text file which I have imported into Excel and contains one column of information which after 40 rows is then repeated for the next person such as
Column A
Name
Address A
Address B
Address C
Postcode
DOB
then repeated again into
Name
Address A
Address B
Address C
Postcode
DOB

There are in fact 40 entries relating to the one person and then after row 40 the next 40 rows relate to the next employee and this is repeated for the whole of that column.What I want to do is to split this column into 40 new columns such as
Column A Column B Column C Column D Column E Column F
Name Address A Address C Address C Postcode DOB

This will give me a file with 40 columns with each row consisting of information about the one employee
Any ideas?

 

[VoG]

Try

Sub tocols()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR - 39 Step 40
    Range("A" & i).Resize(40).Copy
    Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
End Sub

 

[CharlesChuckieCharles]

another approach

Sub transposeRec()
Dim Inputrowct As Long
Dim Outputrowct As Long
Dim OutputColct As Long
Dim xldepth As Long
ActiveSheet.Range("A1").End
Selection.End(xlDown).Select
xldepth = ActiveCell.Row
Outputrowct = 1
OutputColct = 2
For x = 1 To xldepth
ActiveSheet.Cells(Outputrowct, OutputColct) = ActiveSheet.Cells(x, 1)
Debug.Print Cells(x, 1).Address
OutputColct = OutputColct + 1
If OutputColct = 42 Then ' hit record size one column over and one column more
OutputColct = 2
Outputrowct = Outputrowct + 1
End If

Next x
End Sub

 

[gregtx81]

And a third approach... (just for fun; VBA is probably more appropriate)

Assuming info starts in A1, put this in B1 and paste into columns B:AO

=OFFSET(B1,(ROW()-1)*39+(COLUMN()-2),-COLUMN()+1)

 

[MikeWx]

And a fourth approach, where STUFF is the range name for your column of text. Copy this formula adjacent to STUFF and accross the forty columns. If you select the forty columns and let it copy down, it will put empty after it runs out of STUFF data to move. This requires Excel 2007 because of the IFERROR function.

=IFERROR(INDEX(STUFF,COLUMN(A1)+(40*ROW(A1)-40),1),"")