Hello
I have series of data by different months in different columns, instead of having the data in columns, i need to stack it in rows one below each other.
I have been trying to do this in pivot , i am not able to get this?
is there a pivot solution or only vba or some excel formulas would do?
I have sample excel file attached to explan clearly.
Any guidance will be really helpful.
Your file shows data "grouped" in blocks (rows) of 5: is this always true or do the "blocks" vary in the number of rows?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Re: Easiest way to transpose columns data in to rows one below each other
Power Query
It varies , not always in group of 5
Thanks Sandy, Do you need to have the power query to do this as i dont seem to have license for this?
masa.png
XL2010 and XL2013 require Power Query add-in (link), it's free from MS site, all higher versions have Power Query built in
Last edited by sandy666; 11-21-2022 at 10:21 AM. Reason: added link
Hi. Before we get going on this one...
Is your forum profile showing the Excel PRODUCT that you need this request to work with?
The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.
Cheers,
Glenn.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
L4, copied down:
=IFERROR(INDEX($G$3:$J$3,,1+INT((ROWS(L$4:L4)-1)/COUNTA($G$4:$G$13))),"")
M4 copied across and dnown:
=IF($L4="","",INDEX(B$4:B$13,1+MOD((ROWS(M$4:M4)-1),COUNTA($B$4:$B$13)),))
R4, copied down:
=IF($L4="","",INDEX($G$4:$J$13,1+MOD((ROWS(R$4:R4)-1),COUNTA($B$4:$B$13)),1+INT((ROWS(R$4:R4)-1)/COUNTA($B$4:$B$13))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks