Urgent: Distributing data into rows and columns

[rajm]

Please help me on how I could accomplish the following:

Consider an example wherein I have just a single column having 200
rows. I would like the data to be evenly distributed into columns, eac
having 50 rows.

So, in this case it would be 40 columns, each having 50 rows. I woul
sincerely appreciate any help. Thanks

 

[Andy B]

Hi

Have a look here:
http://www.mvps.org/dmcritchie/excel/snakecol.htm

 

[Alan Beban]

Please help me on how I could accomplish the following:

Consider an example wherein I have just a single column having 2000
rows. I would like the data to be evenly distributed into columns, each
having 50 rows.

So, in this case it would be 40 columns, each having 50 rows. I would
sincerely appreciate any help. Thanks.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, assuming
that your data is in A1:A2000, you could array enter into B1:AO1

=ArrayReshape(A1:A2000,50,40)

Alan Beban

 

[macropod]

Hi rajm,

If your data are in ColumnA on Sheet1, put the following formula in Cell A1
on another worksheet (eg Sheet2):
=OFFSET(Sheet1!$A$1,COLUMN()-1+(ROW()-1)*40,)
Copy this formula across for 40 columns and down for 50 rows.

Cheers

 

[Gord Dibben]

Manually............

If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 40 columns and down 50
rows will produce your 40 columns of 50 rows. Any more than 2000 original
rows, you do the math and make alterations.

=INDIRECT("A"&(ROW()+(COLUMN()-2)*50))

The 2 refers to the column of Cell B1; if you're putting the formula in
a different column, use the appropriate number for that column.

Copy>Paste Special(in place) the results then delete the original column A.

VBA Macro to snake the columns top to bottom...1 to 50 down then 51 to 100
down

Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub

VBA macro to snake the columns side to side...1 to 50 left to right then 51 to
100 left to right

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
''Dim nocols As Integer
goagain:
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then GoTo tryover
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
tryover:
Style = vbYesNo
msg = "You Have Cancelled " & Chr(13) _
& "Or Not Entered Criteria" & Chr(13) _
& "Do You Wish To Try Again?"
response = MsgBox(msg, Style)
Set srng = Nothing
If response = vbYes Then GoTo goagain
If response = vbNo Then Exit Sub
On Error GoTo 0
End Sub

Gord Dibben Excel MVP