Way to put multiple rows into the same column.

[Guest]

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.

 

[Mark]

Hi,

Try my EastyText_Rev1.xls at
http://www.geocities.com/excelmarksway

It might work, er, maybe, hmmm...

- Mark

 

[Biff]

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

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

 

[Alan Beban]

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 D19000:

=ArrayReshape(A1:C3000,9000,1)

Alan Beban