CODEWITHSUNDEEP
google-sheets
Marc Jacobs
Marc Jacobs

Reputation: 11

Stacking multiple query output in one sheet

For my department I made a dynamic and flexible overview sheet for every teacher, showing his of hers tutored students.

In one overview sheet I have made several query's, that gather data form different sources (students and teachers in different years). The query's are in one column, because I want to generate a list per teacher.

But the output the query formula generate, can differ in length (= number of rows it takes = number of students), that's is why I have to allow for a fixed number of rows in the overview sheet. And that can lead to ugly blank rows in my list.

Is there a way to keep the flexibility (for every teacher has a different number of tutored students), but remove the blank rows?

Upvotes: 1

Views: 9386

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

If any sheet for every teacher has the same number of same columns, then your data is well-structured and you could make single query on them.

=query({Sheet1!A1:C;Sheet2!A2:C},"select * where Col1 <> ''")

Pay attention on some details:

  1. query should take only one row with headers, use Sheet1!A1:C with headers and the others SheetName!A2:C
  2. when query have source, that contains of multiple tables, use Col1, Col2, Col3... instead of column's letters A, B, C... It may be even more convenient. This rule also works, when data is imported from another file.

Upvotes: 3

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

You can combine ranges using , for columns and ; for rows and by wrapping the ranges in {}. (You need to add the curlies after indicating you are concatenating ranges)
Assuming in Sheet1 you aggregate the data from Sheet2 and Sheet3you can use:

={QUERY(Sheet2!A2:B, "SELECT A, B WHERE A <> ''");
  QUERY(Sheet3!A2:B, "SELECT A, B WHERE A <> ''")}

Where A is the student names (or whatever cannot be empty) and B something arbitrary like marks. Of course that query can be as complex as you want (probably best to prebuild it and reuse it via reference then).

Upvotes: 2

Related Questions