Re: Transpose Problem

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ken Wright (ken.wright_at_NOSPAMntlworld.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 08:30:30 -0000

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
Quantcast