How do I translate from 5Row/20Col to 20Row/5Col ?

[Guest]

I wish to array dat by switching colums to rows and rows to columns without
cutting and pasting for ever. Help please.

 

[Max]

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

 

[Guest]

Excellent. It worked.

If I want to permanently transform the data without keeping the original
array, is there a way I can do it ?

 

[Harald Staff]

Edit - Cut
Edit - Pastespecial - Transpose

HTH. Best wishes Harald

 

[Max]

Edit - Cut
Edit - Pastespecial - Transpose

 

[Guest]

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