Transpond one very long column to several columns of specified len
 

Hi,

I wonder if it is possible to move data from one column with about 20.000
rows into 100 new columns with 200 rows in each column? Of course I can
always use copy/paste/paste special but is there a quicker way?
best regards Magnus

Transpond one very long column to several columns of specified len
 

Assume data in A1 down
In B1:
=OFFSET($A$1,ROWS($1:1)*100-100+COLUMNS($A:A)-1,)
Copy B1 across by 100 cols to CW1, fill down by 200 rows to CW200
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Magnus" wrote:
I wonder if it is possible to move data from one column with about 20.000
rows into 100 new columns with 200 rows in each column? Of course I can
always use copy/paste/paste special but is there a quicker way?

Transpond one very long column to several columns of specified len
 

Max's formula solution works well.

If you want a macro.................??

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
Exit Sub
End Sub

Enter 100 in the inputbox to get the configuration you want.


Gord Dibben MS Excel MVP

On Mon, 19 May 2008 06:31:01 -0700, Magnus
wrote:

Hi,

I wonder if it is possible to move data from one column with about 20.000
rows into 100 new columns with 200 rows in each column? Of course I can
always use copy/paste/paste special but is there a quicker way?
best regards Magnus

Transpond one very long column to several columns of specified
 

Thank you. I liked the idea and the function offset is nice for this purpose
but the formula have some errors and I havn't figured these out yet. The
function requires at least three argument but there's only two here? There is
a , in the end?

"Max" wrote:

Assume data in A1 down
In B1:
=OFFSET($A$1,ROWS($1:1)*100-100+COLUMNS($A:A)-1,)
Copy B1 across by 100 cols to CW1, fill down by 200 rows to CW200
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Magnus" wrote:
I wonder if it is possible to move data from one column with about 20.000
rows into 100 new columns with 200 rows in each column? Of course I can
always use copy/paste/paste special but is there a quicker way?

Transpond one very long column to several columns of specified
 

=OFFSET($A$1;ROWS($1:1)-1+(200)*(COLUMN()-1);0)

This worked as I wanted =)

Thank you

"Max" wrote:

Assume data in A1 down
In B1:
=OFFSET($A$1,ROWS($1:1)*100-100+COLUMNS($A:A)-1,)
Copy B1 across by 100 cols to CW1, fill down by 200 rows to CW200
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Magnus" wrote:
I wonder if it is possible to move data from one column with about 20.000
rows into 100 new columns with 200 rows in each column? Of course I can
always use copy/paste/paste special but is there a quicker way?

Transpond one very long column to several columns of specified
 

=OFFSET($A$1;ROWS($1:1)-1+(200)*(COLUMN()-1);0)

Glad you got it up to suit your actuals (including the delimiter change -
semicolon instead of comma - to suit your excel settings). But there were
*no* errors earlier with the suggested expression. What I illustrated works
"as-is" with the stated assumption on where source data starts. You could
actually have placed the same extract formula in any starting cell on the
sheet (not necessarily in B1), then just copy across from there & fill down
to populate a 100 C x 200 R grid. The last zero in the OFFSET expression is
optional, but the comma (in your case semicolon) is required.

And since there were no errors earlier, would you care to take a moment to
press the "Yes" button below?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Magnus" wrote:
=OFFSET($A$1;ROWS($1:1)-1+(200)*(COLUMN()-1);0)

This worked as I wanted =)

Thank you