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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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
> > >>
> > >>
> > >
> > >
> >
> >
>
>