Re: Transpose Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Date: Mon, 22 Mar 2004 00:13:00 -0000

You just have to flip the second and third argument around and adjust for the
right number of rows/columns, eg for 100 columns assuming you start this in Row
15:-

=OFFSET($A$1,FLOOR((ROW()-15)/100,1),MOD(ROW()-15,100))

Don't forget, Chip's stuff is a lot more generic than what I have just given
you, and uses another function anyway. Given the same parameters, were he to
hardwire in those parameters as i have done here, those formulas would reduce a
lot. I've given you explanations in text, whereas a lot of the explanation in
Chip's formulas is through the formula variables themselves. 

-- 
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:eRECHn5DEHA.3016@TK2MSFTNGP11.phx.gbl...
> 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
>
<snip>
---
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