Columns to Rows

[RColeII]

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?

[rahrah3a]

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]

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]

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?
> > >

[Lewis Clark]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >> > >
> >
> >
> >

[Alan Beban]

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