How do I repeatedly transpose blocks of data?

I have blocks of data that are all in colomn A and the blocks are 27 rows in
length. I want to transpose this data into a database format. I've written
the formula to do 1 line but I need to be able to copy this formula down so
that it moves on 27 rows instead of the 1 that it normally does when you copy
down formula. There is over 1000 blocks of data to transpose. I'm using the
2000 version.

If I read this correctly, you wish to move A1 to A27 to C1 to AC1, and then repeat this process for each 27 rows so that A28 to A54 move to C2 to AC2 etc.

One (slow) way to do this is to number column B (put 1 in B1, then hold the ctrl-key and formula-drag this down the B column to the end of your data), it should sequence equal to the row number.

In C1 put
=INDIRECT("$A"&(($B1-1)*27+1))
and formula drag this to cell AC1, then increment the last numbers so that cell D1 reads
=INDIRECT("$A"&(($B1-1)*27+2))
and on up to AC1 to read
=INDIRECT("$A"&(($B1-1)*27+27))

Select C1 to AC1 (highlight all 27 cells) and formula-drag down 1,000 rows (plus or minus, according to your estimate)

Then copy to a new sheet and paste-special, values, and remove columns A and B

Hope this helps

Originally Posted by LucyW

I have blocks of data that are all in colomn A and the blocks are 27 rows in
length. I want to transpose this data into a database format. I've written
the formula to do 1 line but I need to be able to copy this formula down so
that it moves on 27 rows instead of the 1 that it normally does when you copy
down formula. There is over 1000 blocks of data to transpose. I'm using the
2000 version.

Have you tried to use the TRANSPOSE function. You can view how to use the 2
transpose methods available in Excel on
http://www.auditexcel.co.za/lookupandreference.html .

"LucyW" wrote:

> I have blocks of data that are all in colomn A and the blocks are 27 rows in
> length. I want to transpose this data into a database format. I've written
> the formula to do 1 line but I need to be able to copy this formula down so
> that it moves on 27 rows instead of the 1 that it normally does when you copy
> down formula. There is over 1000 blocks of data to transpose. I'm using the
> 2000 version.

Thanks Bryan - that's exactly what I was looking for.

Just made about 20 hours of work whittle down to one.

Many thanks

Lucy

"Bryan Hessey" wrote:

>
> If I read this correctly, you wish to move A1 to A27 to C1 to AC1, and
> then repeat this process for each 27 rows so that A28 to A54 move to C2
> to AC2 etc.
>
> One (slow) way to do this is to number column B (put 1 in B1, then hold
> the ctrl-key and formula-drag this down the B column to the end of your
> data), it should sequence equal to the row number.
>
> In C1 put
> =INDIRECT("$A"&(($B1-1)*27+1))
> and formula drag this to cell AC1, then increment the last numbers so
> that cell D1 reads
> =INDIRECT("$A"&(($B1-1)*27+2))
> and on up to AC1 to read
> =INDIRECT("$A"&(($B1-1)*27+27))
>
> Select C1 to AC1 (highlight all 27 cells) and formula-drag down 1,000
> rows (plus or minus, according to your estimate)
>
> Then copy to a new sheet and paste-special, values, and remove columns
> A and B
>
> Hope this helps
>
>
> LucyW Wrote:
> > I have blocks of data that are all in colomn A and the blocks are 27
> > rows in
> > length. I want to transpose this data into a database format. I've
> > written
> > the formula to do 1 line but I need to be able to copy this formula
> > down so
> > that it moves on 27 rows instead of the 1 that it normally does when
> > you copy
> > down formula. There is over 1000 blocks of data to transpose. I'm using
> > the
> > 2000 version.
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=391764
>
>