Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transpose rows to column (cell) | Excel Discussion (Misc queries) | |||
How to transpose and concatenate long list in one column? | Excel Worksheet Functions | |||
Transpose Column to Rows | Excel Discussion (Misc queries) | |||
Transpose several columns to one long column | Excel Discussion (Misc queries) | |||
transpose a column into many rows | Excel Discussion (Misc queries) |