+ Reply to Thread
Results 1 to 19 of 19

Combining multiple columns into one

  1. #1
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Combining multiple columns into one

    Hey!

    So I have a very specific task that I am needing to do. And I have at least made a dent in it.

    So if I have this:

    ABCDE
    1 COOKIE WATER CUP FORK APPLE
    2 CAKEJUICE MUG KNIFE PEAR
    3 PIE WINE BOWL TABLE SPOON KIWI
    4 PUDDING BEER GLASS LADLE ORANGE
    5 TART VODKA PLATE BUTTER KNIFE GRAPE
    6 ICE CREAM SODA SAUCER BABY SPOON LEMON
    7 FUDGE COFFEE TEA CUP PLASTIC SPOON PINEAPPLE
    8 CHEESECAKE TEA THERMOS TEASPOON STRAWBERRY
    9 CANDY MILK WINE GLASS PEELER BLUEBERRY
    10 CUPCAKE LEMONADE JUG SPATULA RASPBERRY





    123.png






    I need a formula that will get me all of column A, all of column C, and all of column E into one column, and all of columns B and D in another column So, I need this

    A B
    20 COOKIE WATER
    21 CAKE JUICE
    22 PIE WINE
    23 PUDDING BEER
    24 TART VODKA
    25 ICE CREAM SODA
    26 FUDGE COFFEE
    27 CHEESECAKE TEA
    28 CANDY MILK
    29 CUPCAKE LEMONADE
    30 CUP FORK
    31 MUG KNIFE
    32 BOWL TABLE SPOON
    33 GLASS LADLE
    34 PLATE BUTTER KNIFE
    35 SAUCER BABY SPOON
    36 TEA CUP PLASTIC SPOON
    37 THERMOS TEA SPOON
    38 WINE GLASS PEELER
    39 POT SPATULA
    40 APPLE
    41 PEAR
    42 KIWI
    43 ORANGE
    44 GRAPE
    45 LEMON
    46 PINEAPPLE
    47 STRAWBERRY
    48 BLUEBERRY
    49 RASPBERRY












    3456.png
    The reason that I need a formula is because I have one workbook with multiple sheets that will be used as a master template, and I want to be able to use the =DGET formula in order to have the information I need copied into another worksheet.


    This is the formula that I have been working with, but unfortunately I don't know what all of the parts mean, so I can't fully apply it to my workbook.

    =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

    I've also been trying this one

    =INDEX(MyData,1+MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData)),1+INT((ROW(A1)-1)/COLUMNS(MyData)))

    Is there anyone who can break apart the formula and explain what each part is/means/does?


    If someone suggests a macro, I would need to know what each part is as well so that I can apply it to my particular workbook. That's why I thought a formula might be easier.


    Thanks for any help yall can give me!!
    Last edited by hadydea; 07-12-2019 at 06:59 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    The formulas that you have tried will only merge the columns to one list in order, to split them as you need takes a little more manipulation.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I'll assume that you have a named range called 'MyData' already created, if not, you will need to create one or edit the formula so that it referes to the actual range where the data is located.
    For clarity, the range that MyData refers to is the upper table, A1:E10.

    The references to A14 in the formula should all point to the cell where you enter the first formula before dragging it around to fill the rest of the table. Please give special attention to the positions of the $ symbols, these parts of the formula are used as counters, an incorrectly placed $ symbol will cause incorrect counts and incorrect results in the table.

    To better understand the formula, I would advise using the evaluation tool on the Formulas tab of the Excel ribbon.

    This part
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    works as a row counter, as you fill down it compares the number of rows with the formula to the number of rows in the source data. If you check the help file for the information on the MOD function, you will see that MOD(10,10) will return 0 (no remainder), while this is correct, for the formula to work correctly, it needs to be 10, the adjustments of -1 before the operation and +1 after give the result needed.

    The next part of the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    works in a similar way by using a count of the rows to determine the correct column. This time, the formula is using decimals instead of post division remainder, (1/10, 2/10, 3/10, etc) The result is then truncated and multiplied to step right as needed.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Combining multiple columns into one

    This is a fairly easy task with Power Query. Suggest you upload your sample file as I cannot manipulate a picture to demonstrate and do not wish to try and re-create your file since you already have created it.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    So what I need to do is combine columns C, H, M, R, W, AB, AG, AL, AQ into one column. But the rows need to go in order. meaning it needs to appear like this
    C5
    H5
    M5
    R5
    W5
    AB5
    AG5
    AL5
    AQ5
    C6
    H6
    ETC

    We have creative namers that put their data in at different times, so I need a formula that gathers all their names into one column so that I can then use the =dget function to move them into other worksheets automatically.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Combining multiple columns into one

    Is this the correct workbook? It contains just empty tables ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    Yes, they are the correct tables. I can't post anything in the tables because of confidentiality at work. But they can contain anything. I just need to figure out how to combine the columns in the proper way for future use.

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    Quote Originally Posted by hadydea View Post
    But the rows need to go in order. meaning it needs to appear like this
    C5
    H5
    M5
    R5
    W5
    AB5
    AG5
    AL5
    AQ5
    C6
    H6
    ETC
    So what you're saying is that you want a solution that reads left to right then top to bottom, while your original example clearly shows the results being read from the table top to bottom then left to right (A1,A2,A3,C1,C2,C3 etc).

    This is as unhelpful as an empty table to anyone trying to assist you. If you can't post actual data, simply fill it with fictional data. Many people have Mr M Mouse and Mr D Duck on their payroll.

    The key thing is to make the fictional data an accurate representation of your real data by keeping the format as close as possible.

    Enter the results manually where you want the formulas to go (copy and paste works with text data), making sure that the results shown are as you need them so that somebody trying to help you doesn't waste their time writing a formula with an explanation that will be of no use to you. When this happens, people can become reluctant to help you based on the assumption that it could happen again.

  8. #8
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    Sorry about that. I've been working on it for a few days and I got myself a little turned around.
    C5
    H5
    M5
    R5
    W5
    AB5
    AG5
    AL5
    AQ5
    C6
    H6
    ETC

    That is how it should be. I entered some data into the table. Does this help? I just need a formula and the one I previously posted is the one that has gotten me the closest, but I've only figured out how to get the rows pulled consecutively instead of in the way I need them pulled.
    =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    Is the data in your sample meant to be a 'before' sample or an 'after' sample? Whichever one it is meant to be, it is of no use without the other!

    Give this a go, see my earlier post for some explanation of how it works, it has been re-ordered but the method is similar.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the use of /2 and *2 in the formula equates to the offset between the columns, in your first example A to C is 2 columns, as is B to D, C to E, etc. You get the idea.

    In the absense of a useful and accurate data sample I've assumed that 'MyData' contains an even number of columns and that the columns being extracted by the formula will be in equal proportion, i.e. A,C and E into the first column, B,D and F into the second. It will not work with 5 columns as shown in post #1!

  10. #10
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    Jason! This is almost exactly what I need!!!! The last part I need is...How do I get it to start at C? I've got everything but that figured out!!

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    If the range 'MyData' is set correctly to your actual data then the starting point should be correct.

    If you enter the formula into one cell, then drag it to the right before dragging down, then the mix of absolute and relaive row and column references will adjust everyting automatically, you don't need to make any changes for the second column of results.

    If you need the formula to ignore the first column of 'MyData' then changing +1 to +2 and changing +COLUMNS($A14:A14) to +COLUMNS($A14:A14)+1 should work.

  12. #12
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    I tried sending you a private message, but I don't think the picture appeared correctly. This is what is happening.
    Attachment 632279

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    Using private messaging to ask for additional help is against forum rules, you should post your reqeust here.

    Also, the picture is not appearin gin pst #12 either, to attach an image, I think that you need to use the same method as when you attached the sample workbook earlier rather than the insert image icon on the toolbar.

  14. #14
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    Sorry about that! I didn't know about the rules.

    I used the same method as before. I'm not sure why it isn't working.

    I tried again. Can you see it this time?
    Last edited by hadydea; 07-15-2019 at 07:43 AM.

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    Looks like 5+ is the problem (twice in the formula) adding extra calculations like that will almost always be problematic.

    If you check the original formula, you will note that there is a +1 adjustment at the end of the row parameter of index, and no adjustment in the column parameter (this is calculated by the other functions).

    If 'MyData' is defined correctly so that it only includes the data range, not everything else around it, then the adjustments in the formula should not be changed.

    If the formula is not giving the correct results then I will need the actual range of 'MyData' in order to make the correct adjustments.

  16. #16
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    The +5 is what makes is making it start at C5 for results. I need it to pull back only the results for columns C and H in this particular example.

    I have attached a sample workbook for you. The data goes until Row 50.
    Last edited by hadydea; 07-15-2019 at 07:42 AM.

  17. #17
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    It's a badly defined range that is not helping, ideally 'MyData' should start at B5, but then the empty column F would mess things up.

    This works with your existing range definition.

    In A57
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that only the +5 in red refers to the starting row, the other 5's all refer to the number of columns per table (A:E for the first table and F:J for the second).
    The underscored section should equate to the data column in relation to the table, COLUMNS($A57:A57)+2 =3, column C is the 3rd column in A:E, column H is the 3rd in F:J.

  18. #18
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Combining multiple columns into one

    OH MY GOSH IT'S WORKING!!!! THANK YOU SO MUCH! OH MY GOSH!

    I really appreciate all the patience you've had with me! This is a project I've been working on since Friday morning. And I was starting to lose hope that I'd ever get my sheet working! Thank you so much!

    I'd like to learn more about each part of the formula. Like, what each part controls. You said that there was an evaluate button on the formulas tab. Will that explain the different parts of the formula? The only reason I got as far as I did before I made my first post was because of hours of googling!

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Combining multiple columns into one

    The evaluate button shows the formula calculations step by step so that you can see how it gets to the final result.

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Combining Multiple Columns
    By lreed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2019, 05:05 PM
  2. Combining One Record w Multiple Rows into One Row Multiple Columns
    By IXLADXI in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-22-2018, 05:35 AM
  3. [SOLVED] combining multiple columns
    By cwyenberg in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-03-2015, 01:46 AM
  4. Combining data in multiple columns(all columns have same length)into one column
    By nzi0001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-24-2014, 11:19 AM
  5. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  6. [SOLVED] Combining Multiple Columns to Create Multiple Rows in Macro
    By TacoBrew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2013, 04:26 PM
  7. Combining Multiple Columns into One
    By J.J. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2006, 11:30 AM

Tags for this Thread

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