Transpond one very long column to several columns of specified len

  • Thread starter Thread starter Magnus
  • Start date Start date
M

Magnus

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
 
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'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
 
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?
 
=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?
 
Back
Top