+ Reply to Thread
Results 1 to 13 of 13

Stack rows on top of each other

  1. #1
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Stack rows on top of each other

    Hi ...
    How do I stack columns from one sheet on top of each other in another sheet?

    I got 365 columns, each with 24 rows in one sheet.
    I want to stack those 365 columns on top of each other with the correct date in the neighboring column.

    The use of Power Query did not solve this problem.

    Se attached spreadsheet ...
    Last edited by Tresfjording; 05-17-2020 at 12:17 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    456

    Re: Stack rows on top of each other

    Hi,

    Select all you data
    ctrl + c

    select the cell where you want to past to
    right click

    past special
    click past special again

    click transpose
    select what you want values or formulas or all etc...

    click OK
    Please be as complete as possible in your asking so it may save use all the time to rework the solution because you didn't give all the requirements. If you have a layout in mind please work it out first so we can adapt our solution to it. Thanks.
    If you have been helped, maybe you could click the *

  3. #3
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Stack rows on top of each other

    While I think this can be easily solved via PowerQuery Unpivot but I don't know how to use it very well. This is another option using a macro, try it & let me know if it does what you need

    Please Login or Register  to view this content.
    EDIT : I misunderstood the requirement … The code above should do something else, please ignore
    Last edited by nankw83; 05-17-2020 at 09:59 AM.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  4. #4
    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: Stack rows on top of each other

    Curious as to what you tried in Power Query that did not work. I looked at your data and if all the columns are the same in each sheet then a simple Append should work.
    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

  5. #5
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Re: Stack rows on top of each other

    Hi ...
    The PQ could concatenate cells and pivot table, but not bring all columns into one long one. As far as I know.

  6. #6
    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,369

    Re: Stack rows on top of each other

    PQ is able to append and/or merge tables in addition to concatenating cells and pivoting data. I think you should perhaps not make statements about what it can and can't do, as you clearly don't know it well enough to make those judgements. In fact, you have seriously underestimated its capabilities!
    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.

  7. #7
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Re: Stack rows on top of each other

    sorry sendt wrong workbook ...
    Here is the right one ....
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Re: Stack rows on top of each other

    sorry, but I didn't want to state anything. I said; "As far as I know".
    And as far as I'm concerned, please refrain from making judgement about my abilities or lack of such. That's just rude.
    I know about my capabilities, and would like to have an answer to my question, rather than being misjudged as a person.

  9. #9
    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: Stack rows on top of each other

    Using Power Query, highlight the first column. Then Unpivot the other columns. You can then sort the data as required.

    Power Query has many features and can do amazing transformation of data. If you are truly interested in learning more about PQ, then the book "M is for (Data) Monkey" is a wise investment. Available on Amazon
    Attached Files Attached Files
    Last edited by alansidman; 05-17-2020 at 12:19 PM.

  10. #10
    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,369

    Re: Stack rows on top of each other

    Quote Originally Posted by Tresfjording View Post
    Please refrain from making judgement about my abilities or lack of such. That's just rude.
    I know about my capabilities, and would like to have an answer to my question, rather than being misjudged as a person.
    I am sorry that you have misunderstood my comment, which was NOT about your abilities, rather your limited understanding of what PQ can do. You stated categorically in post #1 that PQ cannot do what you need: two of us are questioning this, and Alan has now showed you that it can do what is required.

    I have made no comment about your capabilities, nor have I judged you as a person. I have merely pointed out that PQ can do far more than you have given it credit for.

  11. #11
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Re: Stack rows on top of each other

    okey ....

    May be this forum sin not for me. I made my first worksheet back in the eighties.
    I thought I could get som help here. At least some tips on how to solve the problem.
    Telling me that I know too little, and advise me to buy a book, seems very little helpful to me.

    Have a nice day, all of you.
    I close this thread now.

  12. #12
    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,369

    Re: Stack rows on top of each other

    You have been given help and tips - it's up to you whether or not to act on them. Nobody told you that you know little, just that you don't know enough about PQ to dismiss it as you did.

    Did you look at the workbook that Alan provided? Maybe he can persuade you to give PQ a try.

    Don't cut off your nose to spite your face!

  13. #13
    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: Stack rows on top of each other

    I have provided you with a viable solution. My suggestion to obtain the book was purely a suggestion that said, "If you are truly interested in learning more about PQ"

    If you wish not to buy the book nor learn more about PQ, that is surely your prerogative and is no skin off my nose. Likewise, if you choose not to participate in this forum, that is also your prerogative. Good Luck with your excelling, however you wish to learn or seek help.

+ 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] Transpose multiple columns to multiple rows with Stack
    By Lina2019 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-22-2019, 08:32 AM
  2. [SOLVED] Stack horizontal rows vertically automatically
    By HamSandvich in forum Excel General
    Replies: 8
    Last Post: 09-28-2014, 06:39 PM
  3. [SOLVED] Is there a way to stack the sheet tabs in multiple rows?
    By flyboy54 in forum Excel General
    Replies: 3
    Last Post: 05-01-2013, 08:23 AM
  4. stack word
    By Stews in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-22-2013, 03:10 PM
  5. [SOLVED] Modify code to transpose a stack of data to rows on to delimit on text hone number field
    By coachtim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2012, 02:52 PM
  6. Stack tabs?
    By dawgfan in forum Excel General
    Replies: 1
    Last Post: 10-02-2009, 02:53 PM
  7. [SOLVED] How can I stack 3-D columns
    By jiggiddi in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-15-2005, 12:05 AM

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