transpose: long column -> many rows

[hef]

I need to take a column that goes from a2:a81 and turn it into 27 rows
ie:
a
b
c
d
e
f

needs to be
a b c
d e f

etc...

 

[Gord Dibben]

hef

=INDIRECT("A"&(COLUMN()+(ROW()-2)*3))

Enter this is in B2 then drag across to D2 then drag down to B2828

When happy with the results, copy all and paste special>values. Delete
original column A if wish.

Gord Dibben XL2002

 

[L. Howard Kittle]

Hi Heff,

In B1 enter =A1&A2&A3
In B4 enter =A4&A5&A6
Select B1 through B6.
Pull (lower right hand corner) down to end of column A.
Select the data in column B.
Under Edit > Go To > Special > Go to > Blanks > OK
Under Edit > Delete > Rows Shift Up > OK
Select the data in column B and do a Copy > Edit > Paste Special > Values >
OK

Should get you what you want.

HTH
Regards,
Howard

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Range("A2:C28").Value = ArrayReshape(Range("A2:A81"), 27, 3)

Because A2:A81 is only 80 elements, the 81st element in the 27th row
will be 0.

Alan Beban