Function/macro to relocate RxC to CxR?

[J.Kearney]

Anyone know (have) a function or macro to relocate entire arrays
from rows to columns?

I have a set of calculations that are just easier for me to deal with
in a column format.

i.e.:
A B C ==> A B C
---------- -----------
1 | x y z 1 | x a 6
2 | a b c 2 | y b 7
3 | 6 7 8 3 | z c 8

Thanks in advance.
Joe

[Don Guillett]

Have you looked in help for transpose

--
Don Guillett
SalesAid Software
donaldb@281.com
"J.Kearney" <jwk1@rcn.com> wrote in message
news:sMqdnbtqwdiB3drfRVn-hg@rcn.net...
> Anyone know (have) a function or macro to relocate entire arrays
> from rows to columns?
>
> I have a set of calculations that are just easier for me to deal with
> in a column format.
>
> i.e.:
> A B C ==> A B C
> ---------- -----------
> 1 | x y z 1 | x a 6
> 2 | a b c 2 | y b 7
> 3 | 6 7 8 3 | z c 8
>
> Thanks in advance.
> Joe
>
>

[Max]

Another way (non-array) ..

Assuming source table is in Sheet1, A1:C3

In Sheet2
---------
Put in A1:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)
Copy A1 across to C1, fill down to C3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"J.Kearney" <jwk1@rcn.com> wrote in message
news:sMqdnbtqwdiB3drfRVn-hg@rcn.net...
> Anyone know (have) a function or macro to relocate entire arrays
> from rows to columns?
>
> I have a set of calculations that are just easier for me to deal with
> in a column format.
>
> i.e.:
> A B C ==> A B C
> ---------- -----------
> 1 | x y z 1 | x a 6
> 2 | a b c 2 | y b 7
> 3 | 6 7 8 3 | z c 8
>
> Thanks in advance.
> Joe
>
>

[Max]

To easily adapt the formula to fit other situations, just change the anchor
cell "Sheet1!$A$1" in the OFFSET(...) to point to the top left cell of the
source grid, put the formula in any starting cell and fill across & down by
a "converse" grid size (i.e. xC-yR, if source grid is xR-yC)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

"Max" <demechanik@yahoo.com> wrote...
>Another way (non-array) ..
>
>Assuming source table is in Sheet1, A1:C3
>
>In Sheet2
>---------
>Put in A1:
>=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)
>Copy A1 across to C1, fill down to C3

And why would 3 function calls each in 9 cells, so total 27 function calls,
ever be preferable to one TRANSPOSE call? Further, your formula relies on
where you enter it, so easily fubarred by inserting or deleting
rows/columns.

If the OP's data were static, Edit > Paste Special > Transpose would be a
better approach than using formulas.

[Max]

"Harlan Grove" <hrlngrv@aol.com> wrote
> And why would 3 function calls each in 9 cells, so total 27 function
calls,
> ever be preferable to one TRANSPOSE call? ..

It's just personal preference, and taken from a pure ease of set-up and
editing angle, nothing more <g>. I do find it much easier to enter and edit
a non-array formula. And to be free from having to grapple with selecting a
precise converse destination grid before array-entering TRANSPOSE (this can
be a mite tough if the source grid is quite large).

> If the OP's data were static, Edit > Paste Special > Transpose would be a
> better approach than using formulas.

Agreed, no ifs or buts here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Don Cameron]

Simplest solution I know

Highlight the cells of the array
Copy
Place cursor at location for top left of new array
Paste - Special - Select transpose

And voila - its done
Doncam

"J.Kearney" <jwk1@rcn.com> wrote in message
news:sMqdnbtqwdiB3drfRVn-hg@rcn.net...
> Anyone know (have) a function or macro to relocate entire arrays
> from rows to columns?
>
> Thanks in advance.
> Joe
>
>