Break up column

H

Hobbes2006

Is there a way to break up a single column of data into multiple columns?

I import data from an external source, a list of names. It's all in one
column. Is there any way to format or program so that Excel will break up the
single column into multiple columns, say 50 each, and place the columns side
by side?

I know if this was in Word, I can format the page as columns, though I can't
specify the number of lines I want in each column.
 
G

Gord Dibben

Hobbes

If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 10 columns and down 50
rows will produce 10 columns of 50 rows. Any more/less than 500 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 etc.


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


Gord Dibben Excel MVP




Is there a way to break up a single column of data into multiple columns?

I import data from an external source, a list of names. It's all in one
column. Is there any way to format or program so that Excel will break up the
single column into multiple columns, say 50 each, and place the columns side
by side?

I know if this was in Word, I can format the page as columns, though I can't
specify the number of lines I want in each column.
Click to expand...

Gord Dibben MS Excel MVP
 
Top