Richard
Assuming data is in column A.......
In B1 enter this formula
=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)
Drag/copy across to F1
Select B1:F1 and drag/copy down until you get zeros.
When happy, copy and paste special(in place)>values>OK>Esc
Delete column A
A macro can go much faster and leave no formulas to deal with.
Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub
You would enter 5 in the inputbox.
Gord Dibben MS Excel MVP
I am using Excel 2003 on Windows.
I have a list of 5000 numbers in a column and I would like this data
transposed in groups of 5 to create 5 columns and 1000 rows
In other words how do I turn:
1
2
3
4
5
6
7
8
9
10
into
12345
678910
thanks for any suggestions
Richard
Gord Dibben MS Excel MVP