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

P

petergblunden

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

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
 
P

petergblunden

Excellent. It worked.

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

petergblunden

Harald
Edit - Cut
Edit - Pastespecial - Transpose
Click to expand...

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
 
Top