How do I repeatedly transpose blocks of data?

L

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.
 
B

Bryan Hessey

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

One (slow) way to do this is to number column B (put 1 in B1, then hol
the ctrl-key and formula-drag this down the B column to the end of you
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 s
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,00
rows (plus or minus, according to your estimate)

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

Hope this helps
 
L

LucyW

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

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

Many thanks

Lucy
 
Top