+ Reply to Thread
Results 1 to 9 of 9

Compile multiple columns in one column - error?

  1. 07-09-2011, 08:46 AM #1
    dim06
    dim06 is offline
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Compile multiple columns in one column - error?

    In one worksheet, I have 633 Columns (one next to the other)
    Each column has 11 values
    I want to construct a column in the same worksheet which includes all the 633 columns in vertical order one after another..

    In other words,
    Imagine the columns: A B C D E F G ...

    The result should be like this:
    A
    B
    C
    D
    E
    F
    G
    .
    .
    .

    *Each column should consist of its 11 values. I dont want any manipulation of the values.

    Any expert to give me a hand? I tried some copy-paste macro codes but I get an error! I have uploaded the excel file to facilitate the approach.

    Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-09-2011, 09:05 AM #2
    MickG
    MickG is offline
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Compile multiple columns in one column - error?

    Possibly !!
    Please Login or Register  to view this content.
    Regards Mick
    Reply With QuoteRegister To Reply
  3. 07-09-2011, 09:21 AM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Compile multiple columns in one column - error?

    or a formula dragged down
    =INDEX($2:$12,IF(MOD(ROW(A1),11)=0,11,MOD(ROW(A1),11)),CEILING(ROW(A1)/11,1))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 07-09-2011, 09:24 AM #4
    dim06
    dim06 is offline
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Compile multiple columns in one column - error?

    Thanks Mick but I did with this code and it constructs a column .. but each column consist of 10values and the 11th is gap..

    I don't know why
    Reply With QuoteRegister To Reply
  5. 07-09-2011, 09:29 AM #5
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Compile multiple columns in one column - error?

    Try the an alternative

    Please Login or Register  to view this content.
    tweak the code for your need
    Reply With QuoteRegister To Reply
  6. 07-09-2011, 09:36 AM #6
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Compile multiple columns in one column - error? 

    Please Login or Register  to view this content.
    Last edited by snb; 07-09-2011 at 09:41 AM.

    Reply With QuoteRegister To Reply
  7. 07-09-2011, 09:44 AM #7
    dim06
    dim06 is offline
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Compile multiple columns in one column - error?

    With the formula works great! I don't know how to thank you guys! You made my day cause it saves me a lot of time with my dissertation (its not related to IT).

    I was wondering also if there is any code which searches for the word 'NA' in a worksheet and deletes the 'NA' leaving a Blank space. Replacing by blank space. Have you anything in mind?
    Reply With QuoteRegister To Reply
  8. 07-09-2011, 10:39 AM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Compile multiple columns in one column - error?

    use find replace
    find na replace with nothing
    Reply With QuoteRegister To Reply
  9. 07-09-2011, 11:23 AM #9
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Compile multiple columns in one column - error?

    or:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1