Does anyone know of a function (regular or ASAP Util) thay will allow a
certain range of cells from one column to be cut and pasted across several
columns vertically. For example, I have 2000 cells in column "A" that needs
to be boken down into 18 cells per column pasted horizontally accross the
spreadsheet into other columns (i.e. B, C, D, etc.). Can any one help.

A function can only return a value, not copy and paste rows. Excel will fall
out of the function if you attempt to paste information into another cell.
--
http://HelpExcel.com




"Ms SDB" wrote:

Does anyone know of a function (regular or ASAP Util) thay will allow a
certain range of cells from one column to be cut and pasted across several
columns vertically. For example, I have 2000 cells in column "A" that needs
to be boken down into 18 cells per column pasted horizontally accross the
spreadsheet into other columns (i.e. B, C, D, etc.). Can any one help.

Do you want A19:A36 in B1:B18 and A37:A54 in C1:C18 ?

If your data is in column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across to column DI and down 18
rows will produce 110 columns of 18 rows plus 1 column of 2 rows. *Any more/less
than 2000 original rows, you do the math and make alterations.

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

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.

Or you can use a macro................

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") '112 in this case
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 MS Excel MVP


On Mon, 22 Jan 2007 09:04:01 -0800, Ms SDB
wrote:

Does anyone know of a function (regular or ASAP Util) thay will allow a
certain range of cells from one column to be cut and pasted across several
columns vertically. For example, I have 2000 cells in column "A" that needs
to be boken down into 18 cells per column pasted horizontally accross the
spreadsheet into other columns (i.e. B, C, D, etc.). Can any one help.

Thank you, thank you, thank you!! I would have never thought about the
"Indirect" function. Both the formula and the macro worked exactly like I
wanted it too.

"Gord Dibben" wrote:

Do you want A19:A36 in B1:B18 and A37:A54 in C1:C18 ?

If your data is in column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across to column DI and down 18
rows will produce 110 columns of 18 rows plus 1 column of 2 rows. Any more/less
than 2000 original rows, you do the math and make alterations.

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

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.

Or you can use a macro................

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") '112 in this case
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 MS Excel MVP


On Mon, 22 Jan 2007 09:04:01 -0800, Ms SDB
wrote:

Does anyone know of a function (regular or ASAP Util) thay will allow a
certain range of cells from one column to be cut and pasted across several
columns vertically. For example, I have 2000 cells in column "A" that needs
to be boken down into 18 cells per column pasted horizontally accross the
spreadsheet into other columns (i.e. B, C, D, etc.). Can any one help.

Yay! I guessed correctly.

Thanks for the feedback.

On Mon, 22 Jan 2007 12:34:00 -0800, Ms SDB
wrote:

Thank you, thank you, thank you!! I would have never thought about the
"Indirect" function. Both the formula and the macro worked exactly like I
wanted it too.

"Gord Dibben" wrote:

Do you want A19:A36 in B1:B18 and A37:A54 in C1:C18 ?

If your data is in column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across to column DI and down 18
rows will produce 110 columns of 18 rows plus 1 column of 2 rows. Any more/less
than 2000 original rows, you do the math and make alterations.

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

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.

Or you can use a macro................

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") '112 in this case
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 MS Excel MVP


On Mon, 22 Jan 2007 09:04:01 -0800, Ms SDB
wrote:

Does anyone know of a function (regular or ASAP Util) thay will allow a
certain range of cells from one column to be cut and pasted across several
columns vertically. For example, I have 2000 cells in column "A" that needs
to be boken down into 18 cells per column pasted horizontally accross the
spreadsheet into other columns (i.e. B, C, D, etc.). Can any one help.