Advanced transpose table
Hello forum,
I need to transpose a two column table into horizontal table. However, after making the table horizontal one of the row becomes a header. I want to merge headers into one and combine data into columns under each header. Please see the image below. Its been over an hour and i cannot figure out how to do this.
Thank you for taking time.
2i0qema.jpg
Last edited by pisdukas; 04-10-2019 at 10:04 AM.
You could use Power Query for this (on the Data ribbon - Get & Transform).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.
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.
This solution works:
suppose your original table is at A2:B7. I'll extend it down to B100.
at D2 have this formula:
=INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$2:C2,$A$2:$A$100),0,0),0))
drag across (to F2 and beyond).
Next, place this formula at D3:
=IF(ROWS($D$1:D1)>COUNTIF($A$2:$A$100,D$2),"",INDEX($B$2:$B$100,MATCH(D$2,$A$2:$A$100,0)+ROW(D1)-1))
drag across, then down.
Where did you get the solution? If from another forum, then you will need to read our rules regarding cross posting, please.
Last edited by AliGW; 04-10-2019 at 12:28 PM.
A colleague told me this. I thought that would be useful to know.
Thank you.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks