Unpivot repeating groups of columns into rows

user_4796629 0
Viewed 151

I have a sheet with data in this format Current data

but I would like it reshaped to look like this

Desired Data

I've attempted Transposing the data using the code provided at this answer however the result is not what I want, and I can't seem to get my head around it. I feel like I'm missing something obvious, but the need to work with grouped columns is throwing me a little. I'm used to Python Pandas, and my Excel/Sheets skills are severely lacking.

I've provided all spreadsheets and an example of the use of the linked function in this demo spreadsheet.

Many thanks for any help!

google-sheets
3 Answers

There is a new tab on your sheet called MK.Help. You will find this formula in cell A2. 

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(current_data!A3:A&"|"&current_data!B3:B&"|"&current_data!C3:C&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"size"))&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"colour"))&"|"&FILTER(current_data!D3:L,REGEXMATCH(current_data!D2:L2,"quantity"))),"|",0,0),"where Col4<>''"))

Caveat: I should mention that FLATTEN() is an undocumented function that I only recently discovered. I've believe it is intended to remain "hidden" in the back end of the sheets programming, but if what I did is what you're after, there really isn't a more efficient way to do it. If you're uncomfortable using an undocumented function, let me know and I can try to build a different kind of work around for you. I have spoken to an engineer at Google and was told that it wasn't going anywhere, but there are also no plans to make it "public".

user_110751290

I realised I was misusing the script in the linked answer. I didn't have headings. Once I provided headings to group the columns the unpivot script worked perfectly. I'll leave the example sheets for people to view with the answer.

user_47966290

You could also use this query formula: 

=QUERY({A2:F5;A3:C5,G3:I5;A3:C5,J3:L5},"where Col4<>'' order by Col1")

(no need for a script)

enter image description here

Functions used:

user_15277800
Related