Pulling data from a range of data in another sheet only if cells contain data AND arranging it vertically

Question

I have a Google Sheet created from a Google Form. It is a sign up form for a reading program at a library. The form contains optional fields to register up to 6 kids (providing a name and age for each). So in addition to some initial columns that ask for contact information, there are columns "Participant 1 Name", "Participant 1 Age", "Participant 2 Name," "Participant 2 Age", and so one down to Participant 6. Many people will only be registering 1 or 2 kids, so the rest of those columns will be blank.

Here is a link to an example sheet: https://docs.google.com/spreadsheets/d/1zZ3c7Lk0epT_H1M2Rn_-q5k78oU8JjPqU-cP61DeXQc/edit?usp=sharing

I am trying to create another sheet in the same spreadsheet that gives me a vertical list summary of just the participants and their ages. So in this new sheet, there would be two columns called "Participant Name" and "Age", and this page would import just that data from the forms sheet, ignoring any blank columns.

So far I have tried constructing a query using SELECT and WHERE

=QUERY(FormResponses!F:Q, "select F,H,J,L,N,P where F<>'' or H<>'' or J<>'' or L<>'' or N<>'' or P<>''", 1)

This would just be for the "Name" column and then I thought I could do a similar one for the "Age" column.

It does pull only the data I want (the participant names) from the FormResponses sheet, but it still preserves the rows that they were originally in, whereas I want to list each one vertically. Oddly it also grabbed the column headers and I thought a "1" at the end of the query would keep it from doing that.

I have read other Stack Exchange posts about TRANSPOSE to see if there is a way I can get the data to display vertically but cannot figure out how to apply it within the query.

Answer 1

You may try:

=let(Σ,wraprows(tocol(FormResponses!F2:Q),2),filter(Σ,index(Σ,,1)<>""))