-1

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Oralet
  • 349
  • 1
  • 2
  • 11

1 Answers1

0

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 <> ''

Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
  • Hey Robin, thanks for the reply! I have tried the solution and started to receive this error instead: "Query completed with an empty output." – Oralet Jul 04 '16 at 15:42
  • 1
    Changing condition as "WHERE Col1 is not null" worked but this time it's not combining two sheets, it's just returning the first sheet :/ – Oralet Jul 04 '16 at 15:50
  • OK I figured it out :) "WHERE Col1 is not null" modification works, thanks very much! – Oralet Jul 04 '16 at 16:04
  • You are right, my where clause only works is the whole column contains strings. I'll amend that. – Robin Gertenbach Jul 04 '16 at 16:11