Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Way to put multiple rows into the same column.
I have data that is in the format of 3 columns wide by about 3000 rows. I
want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. |
#2
|
|||
|
|||
Hi,
Try my EastyText_Rev1.xls at http://www.geocities.com/excelmarksway It might work, er, maybe, hmmm... - Mark -----Original Message----- I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. . |
#3
|
|||
|
|||
Hi!
So you want 1 column 9000 rows long? Select column A and insert a new column. Your data will now be in the range B1:D3000. Enter this formula in cell A1 and copy down to A9000 or until you get returns of zero meaning the data has been exhausted: =OFFSET($B$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) Now, with this range still selected do a Copy/Paste Special/Values to convert the formulas to constants. Then you can delete the original data if you want to. Biff -----Original Message----- I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. . |
#4
|
|||
|
|||
Peter Brown wrote:
I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter into D1:D9000: =ArrayReshape(A1:C3000,9000,1) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
How can I convert a data from multiple rows into 1 column? | Excel Worksheet Functions | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) | |||
Can I set a filter for a merged column across multiple rows and o. | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |