Search code examples
google-sheets

Two query in one formula with option A or B or AB

I search for a solution of my query formula. There are two query's together, where sometime data from the first query, the second query or both together. The problem is, when there is no data from the first query, there is an empty cell before the data of the second query. How can I tell, if there is no data from the first query, to hide the empty cell and post the data from the second query ?

={IFNA(QUERY(TRANSPOSE(DATA!AO1:BZ2), "select Col1 where Col2 > 0",0),"");
IFNA(QUERY(ARRAYFORMULA(TO_TEXT(TRANSPOSE(DATA!CA1:CF2))),"select Col2 where Col1 contains '' or Col1 contains 'Ship'", 0),"")}

I try it with IFNA, IF, AND OR but these not working for me.

Solution

  • You may try both these options & see how it goes

    =tocol({QUERY(TRANSPOSE(DATA!AO1:BZ2), "select Col1 where Col2 > 0",0);
QUERY(ARRAYFORMULA(TO_TEXT(TRANSPOSE(DATA!CA1:CF2))),"select Col2 where Col1 contains '' or Col1 contains 'Ship'", 0)},3)

    OR

    =let(Σ,{IFNA(QUERY(TRANSPOSE(DATA!AO1:BZ2), "select Col1 where Col2 > 0",0),"");
IFNA(QUERY(ARRAYFORMULA(TO_TEXT(TRANSPOSE(DATA!CA1:CF2))),"select Col2 where Col1 contains '' or Col1 contains 'Ship'", 0),"")},
        filter(Σ,Σ<>""))