Stacking queries from the same data to one output

Question

Not sure if this is the right way to title this question, but I'm hoping someone can help me. I am generating form responses from parents that want to update their contact information. The form gives the parents the option to enter the name and information of their students, up to four total students. The form would then gather the same 7 pieces of information for each student: Student First Name, Student Last Name, Student E-Mail, Student ID, Student Cell Phone, School Attended, and Grade Level. The parents would then have up to 20 pieces of information to input into the form.

After completing the form, the responses would go into a Google Sheet and I want to parse out the responses to separate sheets based on School Attended. This is the query I have written, but it only works if all of the students that are entered into the form have the same School Attended.

={query('Form Responses 1'!A2:AZ,"Select A,B,C,D,E,F,G,H,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ WHERE G='School Attended' Order by C,B",0); query('Form Responses 1'!A2:AZ,"select A,J,K,L,M,N,O,P,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ WHERE O='School Attended' Order by K,J",0); query('Form Responses 1'!A2:AZ,"select A,R,S,T,U,V,W,X,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ WHERE W='School Attended' Order by S,R",0); query('Form Responses 1'!A2:AZ,"select A,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ WHERE AE='School Attended' order by AA,Z",0)}

In the above code:

A is the timestamp
B:H, J:P, R:X, and Z:AF are the seven pieces of student information
AG:AZ are the 20 pieces of parent/contact information

What I would want is for each school sheet to display the timestamp, the seven pieces of student information, and the 20 pieces of parent/contact information, regardless of the number of students entered on each form or if the students attend different schools. Any help would be amazing.

Thank you!

Aresvik · Accepted Answer · 2021-08-12 19:50:06Z

Presumably column I is something like 'Student 2', Q is 'Student 3' and Y is 'Student 4'? Or are they gaps?

Anyway, it's not 100% clear but I think I know what you're after. You could use an array {} to get the data, then one query to bring back matches for the same school.

Something like this?

=query({'Form Responses 1'!A:H,'Form Responses 1'!AG:AZ;'Form Responses 1'!A:A,'Form Responses 1'!J:P,'Form Responses 1'!AG:AZ;'Form Responses 1'!A:A,'Form Responses 1'!R:X,'Form Responses 1'!AG:AZ;'Form Responses 1'!A:A,'Form Responses 1'!Z:AF,'Form Responses 1'!AG:AZ},"where Col1 is not null and Col7 matches 'School 1' order by Col3,Col2",1)

Or this if you don't want headers:

=query({'Form Responses 1'!A2:H,'Form Responses 1'!AG2:AZ;'Form Responses 1'!A2:A,'Form Responses 1'!J2:P,'Form Responses 1'!AG2:AZ;'Form Responses 1'!A2:A,'Form Responses 1'!R2:X,'Form Responses 1'!AG2:AZ;'Form Responses 1'!A2:A,'Form Responses 1'!Z2:AF,'Form Responses 1'!AG2:AZ},"where Col1 is not null and Col7 matches 'School 1' order by Col3,Col2",0)

Modify 'School 1' for the name of the school.

player0 · Accepted Answer · 2021-08-12 22:14:03Z

0

try:

=QUERY(
 {'Form Responses 1'!A:H,  'Form Responses 1'!AG:AZ;
  'Form Responses 1'!A2:A, 'Form Responses 1'!J2:P,  'Form Responses 1'!AG2:AZ;
  'Form Responses 1'!A2:A, 'Form Responses 1'!R2:X,  'Form Responses 1'!AG2:AZ;
  'Form Responses 1'!A2:A, 'Form Responses 1'!Z2:AF, 'Form Responses 1'!AG2:AZ}, 
 "where Col7='School Attended'
  order by Col3,Col2", 0)

answered Aug 12, 2021 at 22:14

player0

131k14 gold badges92 silver badges149 bronze badges

Collectives™ on Stack Overflow

Stacking queries from the same data to one output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related