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.

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.

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.

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.

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

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.