2

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!

player0
  • 124,011
  • 12
  • 67
  • 124
James Allen-Robertson
  • 551
  • 2
  • 17

3 Answers3

2

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".

MattKing
  • 7,373
  • 8
  • 13
  • Thanks for this Matt. That's one hell of a formula, but it is more transparent than the script I'm using so I like it. I will have to pick over it to make sure I understand each sfage – James Allen-Robertson May 15 '20 at 19:51
0

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.

James Allen-Robertson
  • 551
  • 2
  • 17
  • I can do this with a fairly simple single arrayformula if you're interested? If your script is good then so be it :) but if you're interested in how to do it with a formula, maybe change the sheet to editable, and I can demo it on a new tab. – MattKing May 15 '20 at 18:34
  • Hi @MattKing. That would be really helpful, and if there is a simpler native solution I'd like to see it. I think I've made the doc editable now. Thanks! – James Allen-Robertson May 15 '20 at 19:24
  • Sure thing! is it definitely just 3 "products"? would you like it to plan for indefinite numbers of products? – MattKing May 15 '20 at 19:37
  • 3 was just for the example. It is more an indefinite figure. Thanks! – James Allen-Robertson May 15 '20 at 19:49
0

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:

marikamitsos
  • 10,264
  • 20
  • 26