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

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

"markx" wrote in message
...
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

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

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

"JulieD" wrote in message
...
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" wrote in message
...
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

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


"markx" wrote in message
...
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

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

"JulieD" wrote in message
...
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" wrote in message
...
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

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


"markx" wrote in message
...
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

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

"JulieD" wrote in message
...
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" wrote in message
...
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

Thanks a lot folks,
You are great!!


"Peo Sjoblom" wrote in message
...
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


"markx" wrote in message
...
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

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

"JulieD" wrote in message
...
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" wrote in message
...
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