convert # of columns into a single column

  • Thread starter Thread starter Babu
  • Start date Start date
B

Babu

Friends

I have data in A1:Z10
This should be converted into a single column data
Like, after A1:A10, B1:B10, C1:C10....so on from A1:A100

Thanks in advance
Babu
 
With your data in A1:Z10, in cell A11 put the following formula and copy down:-

=OFFSET($A$1,FLOOR((ROW()-11)/10,1),MOD(ROW()-11,10))

When done, simply copy the data and paste special as values.
 
Ken said:
With your data in A1:Z10, in cell A11 put the following formula and copy down:-

=OFFSET($A$1,FLOOR((ROW()-11)/10,1),MOD(ROW()-11,10))

When done, simply copy the data and paste special as values.
Click to expand...

The above copies A1:J1, A2:J2, etc. Interchange the 2nd and 3rd
argumants for the OP's requested result.

Or for a different approach, if the functions in the freely downloadable
file at http://home.pacbell.net/beban are available to your workbook

=ArrayReshape(A1:Z10,COUNTA(A1:Z10),1,"c")

array entered into a column long enough to accommodate the output. It
assumes no blanks in A1:Z10.

Alan Beban
 
Either I or Ken misunderstood you but I thought you wanted A1:A10, then
beneath that B1:B10, beneath that C1:C10,
if that's the case you can use this formula preferably on another sheet like
if the table was in Sheet1 than is Sheet2 in A1 put this formula and copy
down to row 260

=OFFSET(Sheet1!$A$1,MOD(ROW(10:10),10),FLOOR(ROW(10:10)/10,1)-1)
 
It was me :-(

By the way Peo - I'm assuming that's not Mrs Peo we are seeing out there
mailmerging :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Peo Sjoblom said:
Either I or Ken misunderstood you but I thought you wanted A1:A10, then
beneath that B1:B10, beneath that C1:C10,
if that's the case you can use this formula preferably on another sheet like
if the table was in Sheet1 than is Sheet2 in A1 put this formula and copy
down to row 260

=OFFSET(Sheet1!$A$1,MOD(ROW(10:10),10),FLOOR(ROW(10:10)/10,1)-1)
Click to expand...
 
You were wrong, I had to call her and check.. <g>

--

Regards,

Peo Sjoblom


Ken Wright said:
It was me :-(

By the way Peo - I'm assuming that's not Mrs Peo we are seeing out there
mailmerging :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --
Click to expand...
 
Back
Top