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


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

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