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.

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

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

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