+ Reply to Thread
Results 1 to 8 of 8

Stacking multiple columns into one without macros?

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    Arizona
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    14

    Stacking multiple columns into one without macros?

    I have 20 columns that I would like to stack into one (preferably leaving out any blanks).

    These columns are on different sheets (all in the A column), but I can bring them into the same spreadsheet first if need be.

    1 2 3 4
    5 6 7 8
    9 10 11
    12 13
    14

    To:

    1
    5
    9
    2
    6
    3
    7
    10
    12
    14
    4
    8
    11
    13



    Is there a way I can do this without any macros or VBA?

  2. #2
    Registered User
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 w/ Win7
    Posts
    227

    Re: Stacking multiple columns into one without macros?

    Could you confirm your Excel version?
    2010?

  3. #3
    Registered User
    Join Date
    03-02-2015
    Location
    Arizona
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    14

    Re: Stacking multiple columns into one without macros?

    "Microsoft Excel for Microsoft 365 MSO" 32-bit
    v2108

  4. #4
    Registered User
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 w/ Win7
    Posts
    227

    Re: Stacking multiple columns into one without macros?

    Solution proposed:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,462

    Re: Stacking multiple columns into one without macros?

    1. Please update your version in your profile.
    2. If you can bring all the columns into 1 sheet (say from Col A to Col T, you could try this:

    =LET(Rng,A1:T20,r,ROWS(Rng),c,COLUMNS(Rng),s,SEQUENCE(r*c,,0),i,INDEX(Rng,MOD(s,r)+1,s/r+1),FILTER(i,i<>0))

    The only thing you need to control in this formula is right after "Rng" (where it says A1:T20). Change A1:T20 to whatever range the columns are that you want to merge into one.

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,462

    Re: Stacking multiple columns into one without macros?

    Another option if you keep all the columns on different sheets is to use this formula:

    =LET(
    a, CHOOSE({1,2},Sheet1!A1:A20,Sheet2!A1:A20),
    b, SEQUENCE(ROWS(a)*COLUMNS(a),,0),
    c, FILTER(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1),NOT(ISERROR(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1)))),
    FILTER(c,c<>0))

    The only things you need to change on this formula is after the "Choose", within the brackets, enter in sequential numbers starting with 1 to the number of columns you want to combine, then right after that, list the range of the columns (Sheet1!A1:A20, etc.)
    Last edited by Gregb11; 11-25-2021 at 11:32 PM.

  7. #7
    Registered User
    Join Date
    03-02-2015
    Location
    Arizona
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    14

    Re: Stacking multiple columns into one without macros?

    Quote Originally Posted by Gregb11 View Post
    Another option if you keep all the columns on different sheets is to use this formula:

    =LET(
    a, CHOOSE({1,2},Sheet1!A1:A20,Sheet2!A1:A20),
    b, SEQUENCE(ROWS(a)*COLUMNS(a),,0),
    c, FILTER(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1),NOT(ISERROR(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1)))),
    FILTER(c,c<>0))

    The only things you need to change on this formula is after the "Choose", within the brackets, enter in sequential numbers starting with 1 to the number of columns you want to combine, then right after that, list the range of the columns (Sheet1!A1:A20, etc.)
    This is PERFECT and exactly what I wanted. Thank you!!!!!!!!



    Quote Originally Posted by DJunqueira View Post
    Solution proposed:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you for the help. This broke my cells that had multiple words into separate cells. I have what I wanted with Greg's answer, but I do appreciate the time you took to try and help. Repped!
    Last edited by justinx; Yesterday at 12:11 AM.

  8. #8
    Registered User
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 w/ Win7
    Posts
    227

    Re: Stacking multiple columns into one without macros?

    Quote Originally Posted by justinx View Post
    Thank you for the help. This broke my cells that had multiple words into separate cells. I have what I wanted with Greg's answer, but I do appreciate the time you took to try and help. Repped!
    This happen because the exemple given.
    Good lock, be happy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] looking up data from multiple columns and stacking them into one column
    By ivega29 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2021, 03:24 PM
  2. Taking multiple columns and stacking in to one column
    By caripinkert in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2019, 07:33 PM
  3. Replies: 3
    Last Post: 09-27-2018, 12:16 PM
  4. stacking multiple columns into one
    By niktodorov1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-09-2018, 01:18 PM
  5. [SOLVED] Stacking data from multiple columns.
    By paularthur90 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2016, 05:15 AM
  6. Combining Multiple Columns without stacking
    By ATowne20 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-05-2013, 01:41 PM
  7. Stacking columns across multiple sheets
    By mstinson23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2009, 03:08 PM

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