transpose: long column - many rows
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

transpose: long column - many rows
 

hef

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

Enter this is in B2 then drag across to D2 then drag down to B28:D28

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

Gord Dibben XL2002

On Thu, 6 Nov 2003 17:38:11 -0500, hef
wrote:


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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

transpose: long column - many rows
 

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

"hef" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

transpose: long column - many rows
 

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

hef wrote:
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...


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/