Move data

[Its me]

Hi to all
I am a new user to Excel and I have a list of data that is in 6 lines per
record that I want to move to 6 separate columns per record - can this be
done?
The data goes from A1:A1435
Many thanks in advance
Stephen West
Gold Coast, Australia

 

[JLatham]

Here's a VBA solution to the issue. It works on a single sheet - moving data
from the column (A) you specify in the code to rows beginning in a column (C)
that you also specify. You can change the Const values as required for
virtually any similar situation.

To put the code to work, open the workbook, press [Alt]+[F11] to open the VB
editor then choose Insert --> Module and copy and paste the code below into
it. Make any edits to the code you need to (shouldn't need any based on your
request). Close the VB Editor. Choose the appropriate worksheet, then use
Tools --> Macro -->Macros to put it to work.

Sub TransposeColumnToRows()
Const columnToMove = "A"
Const firstNewColumn = "C"
Const firstRow = 1
Const lastRow = 1435
Const groupSize = 6

Dim RC As Long
Dim CC As Integer
Dim groupCount As Long

'this moves column of data in A
'to rows beginning with column C holding 1st element
Application.ScreenUpdating = False ' improve performance speed
For RC = firstRow To lastRow - groupSize Step groupSize
groupCount = groupCount + 1
For CC = 0 To groupSize - 1
Range(firstNewColumn & groupCount).Offset(0, CC) = _
Range(columnToMove & RC).Offset(CC, 0)
Next ' CC loop end
Next ' RC loop end
End Sub

 

[JLatham]

If you want a worksheet formula solution, put this formula into cell C1:

=OFFSET($A$1,(COLUMN()-COLUMN($C4)+(ROW()-ROW(A$1))*6),0)
fill it to the right to column H and then fill the entire group down until
you've gone far enough to capture all of the data in the column ... down to
about row 240.

This just copies the data via formula; if you want to do away with the
entries in column A, start by selecting all of the cells with the formulas in
them and use Edit --> Copy followed by Edit --> Paste Special with 'Values'
ticked. After you do that, you can clear out column A.