convert # of columns into a single column

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
 
K

Ken Wright

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

Alan Beban

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
 
P

Peo Sjoblom

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)
 
K

Ken Wright

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

Peo Sjoblom

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