Can you transpose 1 column into multiple rows creating a table

[Guest]

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

 

[Guest]

Richard, use edit, paste special, transpose, 5 cells at a time. Record a
macro when you do the first transpose and assign it to a button on a tool
bar. You'll then just need to click the button to transpose 5 cells at a
time. As part of the macro, after the first transpose, insert a row at row
1, moving the first transpose down, and delete the first 5 cells in column A.
This way, the macro will always select the first 5 cells in column A and
allways transpose into B1:F1. HTH

 

[David Biddulph]

If your 1 is in A1, then in B1 put the formula
=OFFSET($A$1,COLUMN()-2+(ROW()-1)*5,0)
Copy this across your 5 columns and down your 1000 rows.

 

[Gord Dibben]

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

 

[Guest]

Thank you very much David this is exactly what I wanted.

Regards,

Richard

 

[Herbert Seidenberg]

Or for fans of Pivot Table...
Generate Row and Column with
=INT((ROW()-2)/5)+1
=MOD(ROW()-2,5)+1

 

[Guest]

Thanks a lot guys. I couldn't even figure out one elegant way to do this, and
you have given me a handful of options.

I have lots to learn about Excel.

Thanks again,

Richard

 

[ceohector]

This works great. My only problem is that I have a space/row between entries and it is causing undesired effects. Ideally each block would have it own line.

It worked nicely for the first entry. After that it was shot.

Examples:

Dr. Sarah Quinton Kirkpatrick
20TWENTY
1425 W Main St Unit B
Bozeman, MT 59715
(406) 586-2173
GET DIRECTIONSVIEW PROFILE

Dr. Jeffrey Squire
20TWENTY
1425 W Main St Unit B
Bozeman, MT 59715
(406) 586-2173
GET DIRECTIONSVIEW PROFILE

Dr. Jenny Harper