Formula to convert/transpose columns to rows (and vice versa)

[markx]

Hello everybody,

I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would
transpose columns to rows (or vice versa) by simple dragging of the cell.
Are you aware of any, hopefully not too complex?

Thanks a lot for your help on this,
Mark

 

[JulieD]

Hi

not sure if it fits the description of not too complex and doesn't work by
dragging, but there is a transpose function
say you have
.........A..........B
1.....Cat........10
2.....Dog.......15
3....Rat..........20

and you want to transpose this table to the range A5:C6
select A5:C6
type
=TRANSPOSE(A1:B3)
and then press Control & Shift & Enter - not just Enter and the data will be
transposed.

Cheers
JulieD

 

[markx]

Thanks Julie,
In fact, I've tested this function just before, but as you pointed it out,
it doesn't work by dragging it.

I remember that some time ago, I've found somewhere on the net two functions
that work as I would like, but concern only transposing rows to columns.
One involves INDIRECT, ADDRESS and ROW functions, the other goes with INDEX
and ROW function. Unfortunately I can't find them back right now to quote
them directly.

While searching, I've just found something similar on the groups, I post it
here for the illustration purposes, maybe this could help you imagine a
"general" formula covering my problem (unfortunately I'm unable to do it by
myself:-

=INDEX(Sheet1!$1:$1,ROW()*2-1) => this one is supposed to skip every second
value, still row to column

or
=INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) => still row to column, a
particular case

or
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1);4);"1";"")&"38") => still
row to column, same post as before

Thanks once again for your help and FU on this!
Mark

---------------------

 

[N Harkawat]

Say you have data from A1 thru B4 and you want this data transposed from
cells D5 thru G6
on cells D5 type
=OFFSET(INDIRECT(ADDRESS(1,ROW()-4)),COLUMN()-4,0)
and copy it across and down

 

[Peo Sjoblom]

Column with values starting in A2 going down

=OFFSET($A$2,COLUMN(A:A)-1,)

copy across and it will return A2, A3, A4 etc

Row with values starting in D2

=OFFSET($D$2,,ROW(1:1)-1,)

copy down and it will return D2, E2, F2 etc

 

[markx]

Thanks a lot folks,
You are great!!

Column with values starting in A2 going down

=OFFSET($A$2,COLUMN(A:A)-1,)

copy across and it will return A2, A3, A4 etc

Row with values starting in D2

=OFFSET($D$2,,ROW(1:1)-1,)

copy down and it will return D2, E2, F2 etc

--

Regards,

Peo Sjoblom


work