Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Break up column
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Break up column
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. CopyPaste 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 On Tue, 28 Nov 2006 14:44:00 -0800, Hobbes2006 wrote: 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. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |