Columns to Rows
 

I need to take a worksheet with columns A1, B1, C1, D1 and reformat those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

copy the selection you want to reformat. Select an empty cell of where you
want your column to begin. Select Edit - Paste Special - in the lower right
check the box for "Transpose" This will reformat the rows into a column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on the
new worksheet = A5 on the original (instead of A2).

"rahrah3a" wrote:

copy the selection you want to reformat. Select an empty cell of where you
want your column to begin. Select Edit - Paste Special - in the lower right
check the box for "Transpose" This will reformat the rows into a column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

Ok... on further tries I was able to get the first 4 columns to paste to the
first 4 rows on the new spreadsheet using the "Transpose" function. However,
Worksheet 1 has 882 rows with 4 columns each. Doing a mass "copy" / "paste
special" didn't work (Copy area and Paste area are not the same size). I
tried Paste Special with 1 cell, ALL cells, 882 cells selected. All with the
same results.

Any ideas on how to duplicate this across the entire spreadsheet?

"RColeII" wrote:

I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on the
new worksheet = A5 on the original (instead of A2).

"rahrah3a" wrote:

copy the selection you want to reformat. Select an empty cell of where you
want your column to begin. Select Edit - Paste Special - in the lower right
check the box for "Transpose" This will reformat the rows into a column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

Excel has a limit of 256 columns, so if you desire to have 4 rows and 882
columns it will not work.

If the number of rows won't grow, you may want to consider dividing your
4x882 range into ranges of about 4x221. Then you can align these 4 ranges
vertically, and put a few blank rows between them as a separator.



Depending on your application,

"RColeII" wrote in message
...
Ok... on further tries I was able to get the first 4 columns to paste to
the
first 4 rows on the new spreadsheet using the "Transpose" function.
However,
Worksheet 1 has 882 rows with 4 columns each. Doing a mass "copy" / "paste
special" didn't work (Copy area and Paste area are not the same size). I
tried Paste Special with 1 cell, ALL cells, 882 cells selected. All with
the
same results.

Any ideas on how to duplicate this across the entire spreadsheet?

"RColeII" wrote:

I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until
A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on
the
new worksheet = A5 on the original (instead of A2).

"rahrah3a" wrote:

copy the selection you want to reformat. Select an empty cell of where
you
want your column to begin. Select Edit - Paste Special - in the lower
right
check the box for "Transpose" This will reformat the rows into a
column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat
those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

What I want to do is take my 4 columns with 882 rows and convert them into 1
column with 3528 rows.

"Lewis Clark" wrote:

Excel has a limit of 256 columns, so if you desire to have 4 rows and 882
columns it will not work.

If the number of rows won't grow, you may want to consider dividing your
4x882 range into ranges of about 4x221. Then you can align these 4 ranges
vertically, and put a few blank rows between them as a separator.



Depending on your application,

"RColeII" wrote in message
...
Ok... on further tries I was able to get the first 4 columns to paste to
the
first 4 rows on the new spreadsheet using the "Transpose" function.
However,
Worksheet 1 has 882 rows with 4 columns each. Doing a mass "copy" / "paste
special" didn't work (Copy area and Paste area are not the same size). I
tried Paste Special with 1 cell, ALL cells, 882 cells selected. All with
the
same results.

Any ideas on how to duplicate this across the entire spreadsheet?

"RColeII" wrote:

I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until
A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on
the
new worksheet = A5 on the original (instead of A2).

"rahrah3a" wrote:

copy the selection you want to reformat. Select an empty cell of where
you
want your column to begin. Select Edit - Paste Special - in the lower
right
check the box for "Transpose" This will reformat the rows into a
column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat
those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

I guess the best example I can think of is:

I want this table:

Horse Cat Fish Sheep
Cow Dog Bird Duck

To become this:

Horse
Cat
Fish
Sheep
Cow
Dog
Bird
Duck

"RColeII" wrote:

What I want to do is take my 4 columns with 882 rows and convert them into 1
column with 3528 rows.

"Lewis Clark" wrote:

Excel has a limit of 256 columns, so if you desire to have 4 rows and 882
columns it will not work.

If the number of rows won't grow, you may want to consider dividing your
4x882 range into ranges of about 4x221. Then you can align these 4 ranges
vertically, and put a few blank rows between them as a separator.



Depending on your application,

"RColeII" wrote in message
...
Ok... on further tries I was able to get the first 4 columns to paste to
the
first 4 rows on the new spreadsheet using the "Transpose" function.
However,
Worksheet 1 has 882 rows with 4 columns each. Doing a mass "copy" / "paste
special" didn't work (Copy area and Paste area are not the same size). I
tried Paste Special with 1 cell, ALL cells, 882 cells selected. All with
the
same results.

Any ideas on how to duplicate this across the entire spreadsheet?

"RColeII" wrote:

I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until
A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on
the
new worksheet = A5 on the original (instead of A2).

"rahrah3a" wrote:

copy the selection you want to reformat. Select an empty cell of where
you
want your column to begin. Select Edit - Paste Special - in the lower
right
check the box for "Transpose" This will reformat the rows into a
column.

"RColeII" wrote:

I need to take a worksheet with columns A1, B1, C1, D1 and reformat
those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?

RColeII wrote:
I guess the best example I can think of is:

I want this table:

Horse Cat Fish Sheep
Cow Dog Bird Duck

To become this:

Horse
Cat
Fish
Sheep
Cow
Dog
Bird
Duck


If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
consider something like

=ArrayReshape(A1:D2,8,1) array entered into an 8-cell column.

Alan Beban