+ Reply to Thread
Results 1 to 8 of 8

stacking multiple columns into one

  1. #1
    Registered User
    Join Date
    06-30-2018
    Location
    Chicago
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    14

    Arrow stacking multiple columns into one

    I need to stack multiple columns into one big columns...

    So if the first column was "first names" and the second column was "last names" I would need to stack it as such:

    first names
    bob
    john
    mike
    mark
    last names
    johnson
    sears
    churchill
    evans

    I would like to do this in a dynamic way with a formula, so any names added to the list would be reflected in the "single stacked" column.

    What is the best way to do this... and actually I would like to also exclude the title of each column so the final results would look like this:

    bob
    john
    mike
    mark
    johnson
    sears
    churchill
    evans

    Thank you for the help!

    ********update w/ additional details************
    example excel file attached, with details on how I'll be using it:

    I will be adding additional items in columns F through J

    When I do that, I would like for columns A and B to be automatically updated.

    ...so for example if i add "pear" in cell F8, I want for pear to dynamically populate in cell B7, and "fruit" to populate in cell A8, while the rest of the contents in the column would shift down.

    Because the length of the list will chage, the formula would need to have a way of eliminating blank spaces before the list is populated in column A and B.

    *****************************************
    Attached Files Attached Files
    Last edited by niktodorov1; 07-06-2018 at 10:41 AM. Reason: attaching an example document

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: stacking multiple columns into one

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    06-30-2018
    Location
    Chicago
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    14

    Re: stacking multiple columns into one

    I've added an excel file in the original post with details on how I'll be using it.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: stacking multiple columns into one

    Please check the attached file in which included the vba code.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,775

    Re: stacking multiple columns into one

    This can be done with PowerQuery. Just load the source table into the PQ editor and then choose Unpivot Columns from the Transform ribbon. Sort on the first column, ascending, then close and load to cell A1. Once set up, the query can be refreshed whenever data is added to the source table. Query refresh is on the Data ribbon.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,677

    Re: stacking multiple columns into one

    ARRAY formula ( Pl see note below) in A1 then drag Down
    Please Login or Register  to view this content.
    In B1 then drag down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: stacking multiple columns into one

    Try this
    Enter array formula in cell F2 and drag formula across to I2 and down

    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Unknown
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    fruits grapefruit fruits vegetables grains dairy meat
    2
    fruits papaya grapefruit celery millet milk chicken
    3
    fruits pineapple papaya broccoli quinoa yogurt beef
    4
    fruits avocado pineapple lettuce wheat butter pork
    5
    fruits lemon avocado cauliflower buckwheat cheese fish
    6
    fruits apple lemon cale barley
    7
    vegetables celery apple oat
    8
    vegetables broccoli
    9
    vegetables lettuce
    10
    vegetables cauliflower
    11
    vegetables cale
    12
    grains millet
    13
    grains quinoa
    14
    grains wheat
    15
    grains buckwheat
    16
    grains barley
    17
    grains oat
    18
    dairy milk
    19
    dairy yogurt
    20
    dairy butter
    21
    dairy cheese
    22
    meat chicken
    23
    meat beef
    24
    meat pork
    25
    meat fish
    Sheet: Sheet1
    Last edited by AlKey; 07-09-2018 at 11:57 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: stacking multiple columns into one

    Here is a dynamic way
    Enter this formula to populate headers. As your columns A & B grow, formula will automatically expend.
    Enter in F1 and drag formula across
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the use this formula to populate values from col B.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Both formulas are regular formulas and don't require use of CTRL+SHIFT+ENTER
    Attached Files Attached Files

+ 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] Stacking data from multiple columns.
    By paularthur90 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2016, 05:15 AM
  2. Replies: 3
    Last Post: 11-14-2015, 03:48 PM
  3. Combining Multiple Columns without stacking
    By ATowne20 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-05-2013, 01:41 PM
  4. [SOLVED] Stacking Columns in Excel
    By ssanjju in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2013, 11:12 PM
  5. Stacking many columns into one
    By pickslides in forum Excel General
    Replies: 2
    Last Post: 01-08-2012, 08:04 PM
  6. Add secondary axis without stacking columns
    By swaintwin in forum Excel General
    Replies: 5
    Last Post: 04-20-2010, 06:09 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