Re: Transpose Problem

From: saturnin02 (saturnin02_at_hotmail.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 18:10:55 -0500

Well Ken, It works great and is much simpler (with all due respect to your
peer).
What about if I want to have instead of each column follow another in the
one target column, I want the rows from top to bottom to write to the target
column, in that order (row by row)?
What modifications need I make?

As is now:
    1 2 3
A
B
C

Instead of getting the following (which your formula gives me):

A1
B1
C1
A2
B2
C2

Have

A1
A2
A3
B1
B2
B3
etc.

For the icing on tha cake....
Tx,
S

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:#l7bI7xDEHA.1236@TK2MSFTNGP11.phx.gbl...
> Hence my confusion, and assumptions about your layout.
>
> If I ignore the headers and assume you simply have a 12x100 array in
A1:L100 and
> you would like that to appear in a single column, effectively giving you
your
> 100 columns one on top of the other, then in say cell A15 put the
following and
> copy down as many cells as required to house all the elements from your
array:-
>
> =OFFSET($A$1,MOD(ROW()-15,12),FLOOR((ROW()-15)/12,1))
>
> Note, the hardwired 15 represents the fact that the first formula is going
in on
> row 15, so if you put it in cell A16 to START, then change it to 16 and so
on.
> The hardwired 12 represents the 12 rows of data, so if you only had 10
rows of
> data you would use 10 in there.
>
> If it was a one off operation, then the following link to an article on
John
> Walkenbach's site would give you this data in an easy couple of steps via
the
> use of a Pivot table and it's 'drill to details' option:-
>
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> -------------------------------------------------------------------------- 

--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> news:eOaFOguDEHA.3344@tk2msftngp13.phx.gbl...
> > Ken,
> > Tx for your suggestion.
> > I found the following which works:
> > http://www.cpearson.com/excel/arr2col.htm
> > Check it out.
> > What do u think of it?  Not the simplest perhaps.....
> > S
> >
> > "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> > news:#1IOFXtDEHA.3796@TK2MSFTNGP10.phx.gbl...
> > > Assuming your data looks like this
> > >
> > >     1997    1998    1999 .....
> > > A  Val1     Val2    Val3
> > > B  Val4     Val5    Val6
> > > C  Val7     Val8    Val9
> > >
> > > A1997
> > > B1997
> > > C1997
> > > A1998
> > > B1998
> > > C1998
> > > A1999
> > > B1999
> > > C1999
> > >
> > > Are you looking for
> > >
> > > A   B      C
> > > A 1997  Val1
> > > B 1997  Val4
> > > C 1997  Val7
> > > A 1998  Val2
> > > B 1998  Val5
> > > C 1998  Val8
> > > A 1999  Val3
> > > B 1999  Val6
> > > C 1999  Val9
> > >
> > > as if so, then simply put the following into your ***, given that
your
> > current
> > > data resides in A1:CW13 assuming 12 rows of data plus headers in row 1
and
> > 100
> > > columns of data plus headers in Col A
> > >
> > > Copy A2:A13 and paste into cell A15 (Will fill A15:A25)
> > > In cells B15:B25 put 1997 in each cell
> > >
> > > In cell A26 put =A15
> > > In cell B26 put =B15+1
> > >
> > > Copy A26:B26, select A27:A1114 and paste
> > >
> > > Now in cell C15 put the following and paste down
> > >
> > > =INDEX($A$1:$CW$12,MATCH($A15,$A$1:$A$12,0),MATCH(B$15,$A$1:$CW$1,0))
> > >
> > > This will then use the values in Cols A & B to look up the relevant
value
> > in
> > > your table
> > >
> > > --
> > > Regards
> > >            Ken.......................    Microsoft MVP - Excel
> > >               Sys Spec - Win XP Pro /  XL 97/00/02/03
> > >
> >
> --------------------------------------------------------------------------
> > --
> > > It's easier to beg forgiveness than ask permission :-)
> >
> --------------------------------------------------------------------------
> > --
> > >
> > >
> > >
> > > "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> > > news:u0XB$1sDEHA.1240@TK2MSFTNGP10.phx.gbl...
> > > > Ken, 12 Rows. 100 Columns
> > > > Can be on same *** or other ***--doesn't really matter.
> > > > S
> > > >
> > > > "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> > > > news:eYFxeasDEHA.576@TK2MSFTNGP11.phx.gbl...
> > > > > How many columns and how many rows - Be precise
> > > > >
> > > > > Is it to be on the same *** or another ***?
> > > > >
> > > > > --
> > > > > Regards
> > > > >            Ken.......................    Microsoft MVP - Excel
> > > > >               Sys Spec - Win XP Pro /  XL 97/00/02/03
> > > > >
> > > >
> >
> --------------------------------------------------------------------------
> > > > --
> > > > > It's easier to beg forgiveness than ask permission :-)
> > > >
> >
> --------------------------------------------------------------------------
> > > > --
> > > > >
> > > > >
> > > > >
> > > > > "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> > > > > news:u$8WnmrDEHA.2804@tk2msftngp13.phx.gbl...
> > > > > > Hi,
> > > > > > XL 2002
> > > > > > I have a matrix as such
> > > > > >
> > > > > >     1997    1998    1999 .....
> > > > > > A
> > > > > > B
> > > > > > C
> > > > > >
> > > > > > The values in the cells are vlook up values
> > > > > >
> > > > > > I need the results of above in ONE column, as such:
> > > > > >
> > > > > > A1997
> > > > > > B1997
> > > > > > C1997
> > > > > > A1998
> > > > > > B1998
> > > > > > C1998
> > > > > > A1999
> > > > > > B1999
> > > > > > C1999
> > > > > >
> > > > > > How can I get this quickly and in a "automated" way--by which I
mean
> > > > with a
> > > > > > formula as opposed to copy/paste, etc.
> > > > > >
> > > > > > Tx a million.
> > > > > >
> > > > > > S
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > ---
> > > > > Outgoing mail is certified Virus Free.
> > > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > > Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004
> > >
> > >
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004
>
>