1

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns.

enter image description here

I want to combine the size columns while retaining the name and gender of the respondents, like this:

enter image description here

I know I can use arrays and queries to do this:

={query({A16:D18},"select Col1, Col2, 'Shirts', Col3 where Col1 is not null");
  query({A16:D18},"select Col1, Col2, 'Pants', Col4 where Col1 is not null")}

Which gets me exactly what I want. But I'm trying to make it dynamic in case I need to add more questions in my form about sizes (which increases the # of columns).

Right now if more columns are added, I would have to add another query() line manually and make it look like this:

={query({A16:D18},"select Col1, Col2, 'Shirts', Col3 where Col1 is not null");
  query({A16:D18},"select Col1, Col2, 'Pants', Col4 where Col1 is not null");
  query({A16:D18},"select Col1, Col2, 'Dresses', Col5 where Col1 is not null")}

Is there any way to dynamically add another query() line if I tell it to look for 3 columns now?

I know this seems like weird request if I'm willing to enter in "3" manually - but I want other people in my company to be able to use this spreadsheet and asking them to manually change the # of columns is easier an less error-prone than having them modify the formula.

I know how to dynamically change the Col#, just not the extra query line...

0

There are many ways how to do this and a lot of them can fail when dealt with not-so-skilled users from your company, so I would recommend this solution which is able to account for future possibilities/states without a need for constantly editing and adapting the formula. This formula will look for header and if present it will append whole range.

={"Name", "Gender", "Type", "Size"; QUERY(
 {IF($C$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$C$1&"', Col3"), {"","","",""});
  IF($D$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$D$1&"', Col4"), {"","","",""});
  IF($E$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$E$1&"', Col5"), {"","","",""});
  IF($F$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$F$1&"', Col6"), {"","","",""});
  IF($G$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$G$1&"', Col7"), {"","","",""});
  IF($H$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$H$1&"', Col8"), {"","","",""});
  IF($I$1<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$I$1&"', Col9"), {"","","",""})}, 
 "select * where Col1 is not null order by Col1", 0)}

If you want something more user-friendly you can create a selectable menu... something like:

where N2 cell is:

={"Name", "Gender", "Type", "Size"; QUERY(
 {IF($K$3<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$3&"', Col3"), {"","","",""});
  IF($K$4<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$4&"', Col4"), {"","","",""});
  IF($K$5<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$5&"', Col5"), {"","","",""});
  IF($K$6<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$6&"', Col6"), {"","","",""});
  IF($K$7<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$7&"', Col7"), {"","","",""});
  IF($K$8<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$8&"', Col8"), {"","","",""});
  IF($K$9<>"", QUERY({$A$2:$I}, "select Col1, Col2, '"&$K$9&"', Col9"), {"","","",""})}, 
 "select * where Col1 is not null order by Col1", 0)}

cell J3 is: =TRANSPOSE(C1:1)

and cell K3 range has data validation:

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.