Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I translate from 5Row/20Col to 20Row/5Col ?
I wish to array dat by switching colums to rows and rows to columns without
cutting and pasting for ever. Help please. |
#2
|
|||
|
|||
One way if you need it dynamic, is via using TRANSPOSE()
Assume source range is in Sheet1, in A1:T5 (a 5R x 20C range) In Sheet2, Select A1:E20 (a 20R x 5C converse range) Put in the formula bar: =TRANSPOSE(Sheet1!A1:T5) Array-enter the formula by pressing CTRL+SHIFT+ENTER A1:E20 returns a dynamic transpose of what's in A1:T5 in Sheet1 For a neater look, we could suppress extraneous zeros display via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "petergblunden" wrote in message ... I wish to array dat by switching colums to rows and rows to columns without cutting and pasting for ever. Help please. |
#3
|
|||
|
|||
Excellent. It worked.
If I want to permanently transform the data without keeping the original array, is there a way I can do it ? "petergblunden" wrote: I wish to array dat by switching colums to rows and rows to columns without cutting and pasting for ever. Help please. |
#4
|
|||
|
|||
Edit - Cut
Edit - Pastespecial - Transpose HTH. Best wishes Harald "petergblunden" skrev i melding ... Excellent. It worked. If I want to permanently transform the data without keeping the original array, is there a way I can do it ? "petergblunden" wrote: I wish to array dat by switching colums to rows and rows to columns without cutting and pasting for ever. Help please. |
#5
|
|||
|
|||
"Harald Staff" wrote
Edit - Cut Edit - Pastespecial - Transpose Aha! Guess the OP's statement below was taken too much to heart, Harald <bg .. without cutting and pasting for ever. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Harald
Edit - Cut Edit - Pastespecial - Transpose I must have looked at the Paste Special dialogue box ten thousand times but have never seen 'Transpose I've been too busy looking at 'Paste Link' It worked beautifully. Thanbk you very much Peter Blunden "Harald Staff" wrote: Edit - Cut Edit - Pastespecial - Transpose HTH. Best wishes Harald "petergblunden" skrev i melding ... Excellent. It worked. If I want to permanently transform the data without keeping the original array, is there a way I can do it ? "petergblunden" wrote: I wish to array dat by switching colums to rows and rows to columns without cutting and pasting for ever. Help please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Translate excel formulae | Excel Discussion (Misc queries) | |||
How can I translate a comment to text in a cell? | Excel Discussion (Misc queries) | |||
How do I Translate English-Spanish in Excel | Excel Discussion (Misc queries) |