CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
Vincent Le
Vincent Le

Reputation: 136

Combine multiple sheets with same amount of columns but different amount of rows

Given two sheets that look like this:

Sheet1
a1  b1
a1  b1

Sheet2
a2  b2
a2  b2
a2  b2

How could I query for both sheets and combine all the rows together to get something line

Combined
a1  b1
a1  b1
a2  b2
a2  b2
a2  b2

I have tried the following but none of them worked

  1. ={QUERY({'Sheet1'!A1:B},"select *");QUERY({'Sheet2'!A1:B},"select *")}
  2. =QUERY({'Sheet1'!A1:B;'Sheet2'!A1:B},"select *")

The problem with both is that I don't specify what row to end at for the two sheets. If I changed it to =QUERY({'Sheet1'!A1:B2;'Sheet2'!A1:B3},"select *"), then it would work. The problem with this is that in my actual spreadsheet, the number of rows in the sheets is changing as I am inputting more data and I would rather not have to update the query everytime.

Upvotes: 0

Views: 138

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

You were close. Include the full possible ranges and add a bit to the "Select" clause:

=QUERY({Sheet1!A:B;Sheet2!A:B},"Select * Where Col1 Is Not Null")

This returns only the non-null (i.e., existing) data from each sheet.

Upvotes: 3

Related Questions