+ Reply to Thread
Results 1 to 10 of 10

how to combine several columns into a single column

  1. #1
    jims
    Guest

    how to combine several columns into a single column

    I have data in 50 columns and 30 rows.
    I would like to know how to either copy or move the data in the next columns
    to the next row of the previous column without using "copy and paste" because
    there are too many columns and I think there is a better way to do this.

    What I mean is that I want to have only one column combining all data from
    50 different columns into one column.

    So it looks like this after moving or copying the data from columns into one
    column.

    column A
    ------------
    A1
    ....
    A30
    B1 ( data in the column B is now in the next row in the column A at the 31st
    row)
    ....
    B30
    ......
    ....
    AX1 ( data in the column AX is now in the next row in the column A at the
    1471st row)

    ....
    Ax30



    Thank you
    Jim



  2. #2
    Max
    Guest

    Re: how to combine several columns into a single column

    One play ..

    Assume source data is in Sheet1, A1:AX30

    In Sheet2

    Put in A1:

    =OFFSET(INDIRECT("Sheet1!"&CHAR(INT((ROWS(Sheet1!$A$1:A1)-1)/30)+65)&"1"),MO
    D(ROWS(Sheet1!$A$1:A1)-1,30),)

    Copy A1 down to A780

    (Above strips cols A to Z)

    Put in A781:

    =OFFSET(INDIRECT("Sheet1!A"&CHAR(INT((ROWS(Sheet1!$A$1:A1)-1)/30)+65)&"1"),M
    OD(ROWS(Sheet1!$A$1:A1)-1,30),)

    Copy A781 down to A1500

    (Above strips cols AA to AX)
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "jims" <[email protected]> wrote in message
    news:[email protected]...
    > I have data in 50 columns and 30 rows.
    > I would like to know how to either copy or move the data in the next

    columns
    > to the next row of the previous column without using "copy and paste"

    because
    > there are too many columns and I think there is a better way to do this.
    >
    > What I mean is that I want to have only one column combining all data from
    > 50 different columns into one column.
    >
    > So it looks like this after moving or copying the data from columns into

    one
    > column.
    >
    > column A
    > ------------
    > A1
    > ...
    > A30
    > B1 ( data in the column B is now in the next row in the column A at the

    31st
    > row)
    > ...
    > B30
    > .....
    > ...
    > AX1 ( data in the column AX is now in the next row in the column A at the
    > 1471st row)
    >
    > ...
    > Ax30
    >
    >
    >
    > Thank you
    > Jim
    >
    >




  3. #3
    Earl Kiosterud
    Guest

    Re: how to combine several columns into a single column

    Jim,

    Put this in A31, and copy down.

    =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1)

    Empty cells will give you zero. You can then convert the formulas to hard
    data: Copy the entire column, then paste it over itself with Edit - Paste
    special - Values. Now you don't need the stuff in columns B, C, etc.
    --
    Earl Kiosterud
    www.smokeylake.com

    "jims" <[email protected]> wrote in message
    news:[email protected]...
    >I have data in 50 columns and 30 rows.
    > I would like to know how to either copy or move the data in the next
    > columns
    > to the next row of the previous column without using "copy and paste"
    > because
    > there are too many columns and I think there is a better way to do this.
    >
    > What I mean is that I want to have only one column combining all data from
    > 50 different columns into one column.
    >
    > So it looks like this after moving or copying the data from columns into
    > one
    > column.
    >
    > column A
    > ------------
    > A1
    > ...
    > A30
    > B1 ( data in the column B is now in the next row in the column A at the
    > 31st
    > row)
    > ...
    > B30
    > .....
    > ...
    > AX1 ( data in the column AX is now in the next row in the column A at the
    > 1471st row)
    >
    > ...
    > Ax30
    >
    >
    >
    > Thank you
    > Jim
    >
    >




  4. #4
    Max
    Guest

    Re: how to combine several columns into a single column

    "Earl Kiosterud" <[email protected]> wrote
    ....
    > Put this in A31, and copy down.
    > =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1)


    Much neater, Earl !

    Perhaps with just a typo corrected, in A31, copied down to A1530:
    =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1)

    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  5. #5
    jims
    Guest

    Re: how to combine several columns into a single column

    Dear Mr. Earl and Max,

    It worked fine.
    You gentlemen are just great.
    Thank you very much,
    Jim

    "Max" wrote:

    > "Earl Kiosterud" <[email protected]> wrote
    > ....
    > > Put this in A31, and copy down.
    > > =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1)

    >
    > Much neater, Earl !
    >
    > Perhaps with just a typo corrected, in A31, copied down to A1530:
    > =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1)
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >
    >


  6. #6
    Max
    Guest

    Re: how to combine several columns into a single column

    You're welcome, Jim !
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "jims" <[email protected]> wrote in message
    news:[email protected]...
    > Dear Mr. Earl and Max,
    >
    > It worked fine.
    > You gentlemen are just great.
    > Thank you very much,
    > Jim




  7. #7
    Earl Kiosterud
    Guest

    Re: how to combine several columns into a single column

    Max,

    Actually, it wasn't a typo. Your version captured the entire table. Mine
    only started after the first column of 30 cells, A1:A30, picking up the
    remaining (column B and on), putting it starting in A31. A1:A30 would
    remain. Just a little lazy. My intent was to use the formulas to grab
    columns B:AD, and delete them after the formula cells had been converted to
    hard values with paste special - values. Lotsa ways to git 'r done (Larry
    the cable guy).
    --
    Earl Kiosterud
    www.smokeylake.com

    "Max" <[email protected]> wrote in message
    news:%[email protected]...
    > "Earl Kiosterud" <[email protected]> wrote
    > ...
    >> Put this in A31, and copy down.
    >> =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1)

    >
    > Much neater, Earl !
    >
    > Perhaps with just a typo corrected, in A31, copied down to A1530:
    > =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1)
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >




  8. #8
    Max
    Guest

    Re: how to combine several columns into a single column

    "Earl Kiosterud" wrote:
    ....
    > Actually, it wasn't a typo ..

    Ah, I see it now. Sorry for the earlier mis-interp, Earl.
    I was wondering where the deuce the formula extract for A1:A30 went to <g>
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  9. #9
    Earl Kiosterud
    Guest

    Re: how to combine several columns into a single column

    Max,

    Actually, I think your change is the better, more generalized, solution. It
    gets the entire table, not just the remainder. A bit neater and cleaner.
    --
    Earl Kiosterud
    Virginia Beach, VA USA, GMT-5
    www.smokeylake.com

    "Max" <[email protected]> wrote in message
    news:%[email protected]...
    > "Earl Kiosterud" wrote:
    > ...
    >> Actually, it wasn't a typo ..

    > Ah, I see it now. Sorry for the earlier mis-interp, Earl.
    > I was wondering where the deuce the formula extract for A1:A30 went to <g>
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >




  10. #10
    Max
    Guest

    Re: how to combine several columns into a single column

    Thanks for the view !
    Interesting site btw, and I don't mean just the Excel part of it <g>
    Liked the short 20% demo on "Car wash" ..
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1