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

  • Thread starter Thread starter markx
  • Start date Start date
M

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

---------------------
 
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
 
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
 
Thanks a lot folks,
You are great!!


Peo Sjoblom said:
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
Click to expand...
 
Back
Top