Attached is the link of my question
I would like to transpose the data like that.
My original data could be thousand of lines.
My thought is to make the same number of tables of my column title, then combine the four tables into one. My thought is on the google sheet as well. It might do the work but I would like a nicer solution.
CodePudding user response：
Try
=query(arrayformula(split(flatten({(A3:A4&"♦"&B3:B4&"♦"&C3:E4)}),"♦")),"select * where Col3 is not null")
CodePudding user response：
After having answered this question hundreds of times in the last couple years after the discovery of the FLATTEN() function, i decided to write a custom function for Google AppsScript to do it. While in some sense, a custom function is more opaque than the SPLIT(FLATTEN( formulaic solution you will find all over the forums in the last couple years, it is at least a little easier to understand in it's operation by the user.
In your shared sheet there is a new script file called MKHelp.gs. In it, you will find the code that I wrote to construct the "UNPIVOT()" function.
On a new tab in your sheet, you will find this formula in a tab called MK.Help.
=QUERY(UNPIVOT(A2:B,"V",C2:E,"B",C1:E1,"H"),"where Col3 is not null")
for unpivot to work well it is best surrounded by a query() to weed out unnecessary rows. In your case, it is that the "amount" not be "empty" or "null".
The letters "V","H" or "B" that follow each range describe the "shape" of the input data. Whether it is "vertical", "horizontal" or "Both".