Transposing list of numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jlhcat - 06 Feb 2008 17:47 GMT

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.

Signature

jlhcat

Reply to this Message

Gord Dibben - 06 Feb 2008 18:17 GMT

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 = 7 '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

Gord Dibben MS Excel MVP

>I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
>to transpose it across 7 columns and 58 rows. Is there an easy way to do
>this? I understand I can do the copy/paste special/transpose for 7 at a
>time. Thank you.

Reply to this Message

Ron Rosenfeld - 06 Feb 2008 19:11 GMT

B1: =INDEX($A:$A,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

Fill right to Column H, then select B1:H1 and fill down to row 60 or so.

If your data does not start in A1 -- for example, if it starts in A3, then
merely change the array argument as appropriate:

=INDEX($A$3:$A$500,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

The other arguments remain unchanged.
--ron

Reply to this Message