I Have multiple columns and rows of data. Each row contains only one entry based on a formula. I now need to take the text in that row and place it in a like column with the rest of the data creating only one column with the result. I have attached a sample. I am looking for a formula to have the result seen in column H (LIST)
This is a simple case of unpivotting the data (columns) using Power Query. PQ is available to Excel 2010 (your current profile) and later versions. Here is the Mcode
Additionally, I have included the file in which I made the change. Click on the links in my signature to learn more about PQ.
Please Login or Register to view this content.
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
Thanks Alan, this did get the desired results however is it possible to have the date show up on the same sheet? I will have to use some of my spare time to learn about Power Queries in this time of isolation.
As to post #3: "...have the [data] show up on the same sheet..."
Paste the following into cell H2 and copy down: =IFERROR(INDEX(A2:F2,AGGREGATE(15,6,COLUMN(A2:F2)/(A2:F2<>0),1)),"")
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.
What date should appear? Don't find one in your sample. Where should this date appear?
A really good primer for PQ is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
Thanks all for your help on this. Problem solved!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
Thanks for the Rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks