CODEWITHSUNDEEP
google-sheets
J.Mapz
J.Mapz

Reputation: 511

How to stack certain columns into one based on several columns

We use google forms to gather data for actions on different problems. This is then routed to google sheets for monitoring of actions identified.

I created a trial file to show the input and the expected output, which can be viewed here.

So in here, the Problem, actions, assigned person and timelines will need to be stacked together based on team and activity as shown in the file. I tried to combine the QUERY function with other functions, but still to no avail.

I previously asked about this but with a simpler data set, which can be found here. Now, after several days of brain-twisting to expand this to a more complex data set, I realized that I should have asked based on an exact representation of the data, and not a simple one (I got confident, but didn't realize it was in fact pretty hard - quite a lessons learned from my end).

Again, hoping for your guidance on how this can be done. Thanks!

Upvotes: 0

Views: 53

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

I couldn't find an elegant answer to this - my solution is very clunky. It's based on the idea of using Sequence to generate the right number of rows for the output, then doing a Vlookup in the rolling count of number of entries per row (using Countifs).

The formula for the first two columns is:

=ArrayFormula(split(iferror(vlookup(SEQUENCE(counta(C2:N)/4,1,0),
{query(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,"select Col1"),A2:A&"|"&B2:B},2,false),"|"),"|",,false))

And for column 3:

=ArrayFormula(vlookup(SEQUENCE(counta(C2:N)/4,1,0),{if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),C2:N},
2+(SEQUENCE(counta(C2:N)/4,1,0)
-vlookup(SEQUENCE(counta(C2:N)/4,1,0),if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),1,true))*4,true))

Column 4 is

=ArrayFormula(vlookup(SEQUENCE(counta(C2:N)/4,1,0),{if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),C2:N},
3+(SEQUENCE(counta(C2:N)/4,1,0)
-vlookup(SEQUENCE(counta(C2:N)/4,1,0),if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),1,true))*4,true))

and you have to change 3 to 4 for the next column.

The last column includes to_date:

=ArrayFormula(to_date(vlookup(SEQUENCE(counta(C2:N)/4,1,0),{if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),C2:N},
5+(SEQUENCE(counta(C2:N)/4,1,0)
-vlookup(SEQUENCE(counta(C2:N)/4,1,0),if(A2:A<>"",index(countifs(if(C2:N<>"",row(C2:N)),"<"&if(C2:N<>"",row(C2:N)))/4,0,1),),1,true))*4,true)))

enter image description here

Upvotes: 1

Related Questions