Stacking multiple query output in one sheet

Question

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?

Max Makhrov · Accepted Answer · 2016-03-06 12:20:59Z

3

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:

query should take only one row with headers, use Sheet1!A1:C with headers and the others SheetName!A2:C
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.

answered Mar 6, 2016 at 12:20

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robin Gertenbach · Accepted Answer · 2016-03-05 21:00:16Z

2

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

answered Mar 5, 2016 at 21:00

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

Collectives™ on Stack Overflow

Stacking multiple query output in one sheet

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related