How to stack data of multiple columns into one column?
Hey guys and girls,
For the works on my construction site, i created a daily schedule which represents the concrete elements i want to receive on the stated day.
Now the responsible of the production plant want to receive it as a weekly schedule.
Any idea how i can stack the data of multiple columns into one, based on the week number? No (alphabethic) sorting allowed! So the data of Day 2 follows the data of Day 1.
I hope you can help,
Thanks in advance!
This proposal employs some intermediate steps as modeled at the top of Sheet1.
1. Copy the original data and paste transpose
2. Remove merge and center formatting for A1:A14 and then fill in the blank cells. (Select A1:A14 > Go To Special > blanks > then press the =, up arrow, Ctrl and Enter keys)
4. WK#'s are placed in row 17
5. Paste the following array entered formula** into cell A18:Formula:
Please Login or Register to view this content.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
6. After the array formula is activated copy down and across as needed.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Is this what you want? Done with PowerQuery.
1. Load the data.
2. Transpose the data and fill down the first column.
3. Unpivot the detail columns.
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.
@AliGW, Brilliant!
@AlexanderDK, Forget post #2. To get your data into the format on the Weekplanning sheet use the information in AliGW's post and then paste the following into cell A2 on the Weekplanning sheet:Formula:Please Login or Register to view this content.
Note that this is not an array formula so once entered it may be copied down and across.
Let us know if you have any questions.
@AliGW and JeteMC, it took me a little longer since the PowerQuery and the aggregate-function were something completely new to me.
FYI: Where the given data in my file was actually a copy of data retrieved with formulas, i had to test it if it would still work with the whole table and powerquery setup, but it works great!! The only thing i have to do now is continue planning the concrete elements and updating the data. This will be such a timesaver!!
Thanks a lot for the help!!
PQ is a Pandora’s Box - enjoy!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks