I have a set of data that I receive from a google forms. It has the information of events, with the # of attendees, name of the event and type of event. They can send up to 5 events in one form, and each one uses 5 columns. I need the columns to stack (array?) one under the other if they have data, while they keep the information of the second and third cell of the data set.

I've made an example sheet here. In data tab you can see the information I have, and in "Output I need" what I want (E2:I2, J2:N2, O2:S2, T2:X2: Y2:AC2).


I've been trying with some queries and array formulas I've found in the forum, but couldn't get the result I wanted.

Any help will be much appreciated.

1 Answer 1


Someone already gave me the answer to this. The formula I was looking for is:

=ArrayFormula({DATA!B1:C1\DATA!E1:I1;QUERY({DATA!B2:C\DATA!E2:I;DATA!B2:C\DATA!J2:N;DATA!B2:C\DATA!O2:S;DATA!B2:C\DATA!T2:X;DATA!B2:C\DATA!Y2:AC};"where Col3 is not null";0)})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.