#Value Error on Query - Google Spreadsheet

Question

I'm trying to combine the data in two sheets into another sheet by using the below code:

=UNIQUE(ArrayFormula(query({filter('Sheet1'!A2:B,NOT(ISBLANK('Sheet1'!A2:A)));filter('Sheet2'!A2:B,NOT(ISBLANK('Sheet2'!A2:A)))},"order by Col1")))

It works perfect if both sheets have at least 1 row filled but if either of the tabs are empty, then I receive #Value.

How can I fix this code so that it still works if either of the tabs are empty?

Answer 1

Filter throws an error instead of returning no values, a property that is very annoying in this case. Since you're already using the query command why don't you try this, either one or both ranges can be completely empty.

=UNIQUE(ArrayFormula(query(
  {Sheet1!A2:B; Sheet2!A2:B},
  "WHERE Col1 is not null order by Col1")))

Alternatively if Col1 contains always strings a shorthand is Col1 <> ''