Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function/macro to relocate RxC to CxR?
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 |
#2
|
|||
|
|||
Have you looked in help for transpose
-- Don Guillett SalesAid Software "J.Kearney" wrote in message ... 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 |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "J.Kearney" wrote in message ... 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 |
#4
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#5
|
|||
|
|||
"Max" 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. |
#6
|
|||
|
|||
"Harlan Grove" 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 <atyahoo<dotcom ---- |
#7
|
|||
|
|||
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" wrote in message ... Anyone know (have) a function or macro to relocate entire arrays from rows to columns? Thanks in advance. Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|