How to merge ranges in Google Spreadsheets?

Question

Google Sheets

Tables are formed in different tabs of one file, then I want to combine them (while ignoring empty lines) into one pivot table.
To insert a single range I use QUERY:

=QUERY('Фундамент'!A2:I41; "SELECT * WHERE D!='' OR H!=''"; TRUE)

How to collect multiple ranges from different sheets (tabs)?

Answer 1

Looks like I found a way:

=ARRAYFORMULA({QUERY('Лист1'!A2:I41; "SELECT * WHERE D!='' OR H!=''"; TRUE);QUERY('Лист2'!A2:I41; "SELECT * WHERE D!='' OR H!=''"; TRUE);QUERY('Лист3'!A2:I41; "SELECT * WHERE D!='' OR H!=''"; TRUE)}
)

Answer 2

Hey!
did you find any other ways? because in this method only 1 range is copied, the second one is not pulled up.

Answer 3

You can combine ranges with {}
QUERY({'Sheet1'!A2:I41;'Sheet2'!A2:I41;'Sheet3'!A2:I41}; "SELECT * WHERE D!='' OR H!='' "; TRUE)

Answer 4

Using the filter function, I think it will help. We need to find all non-empty lines in the ranges, so we filter them, and put the ranges into arrays and that 's it
. Here is an example of import code from three different sheets of the table.
=filter({'Client1'!A2:K;'Client3'!A2:K;'Client2'!A2:K};NOT(ISBLANK({'Client1'!A2:A;'Client3'!A2:A; 'Client2'!A2:A})))
P.S. We are looking for empty cells only in the first column.