How to combine dynamic columns in Google Sheets while retaining data in other rows?

Question

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.

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

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

Answer 1

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:

Answer 2

After searching far and wide I came up with this elegant little solution. It uses INDIRECT to add all columns into one as more columns are added. You then use the indirect cell to combine all columns.

1. Specify your INDIRECT range in a helper cell first with
   =B6"&":"&SUBSTITUTE(ADDRESS(1, COUNTA($A$2:$CA$2), 4), 1, "")
   where B6 is the first cell of data and CA is the furthest column you want to capture.

In my sheet, you get the result: (B6:Q), where the data in columns go from B:Q. If you, for example, add a column of data in R, it changes to (B6:R).

Say you put this formula into cell A1, then:

2. Then to combine all the columns into one:
   =UNIQUE(flatten(transpose(INDIRECT(A1))))