Google Sheets, Unsure as why I'm getting this error both quires work-CodePudding

={ QUERY(B2:D41, "SELECT B,C,D WHERE D <> ''", 0); QUERY(B42:B54, "SELECT B WHERE B <> ''",0)}

"In ARRAY_LITERAL, An Array Literal was missing values for one or more rows"

As I mentioned both quires work, and when I alter the first the be

=QUERY(B2:D41, "SELECT B,C,D WHERE C <> ''", 0)

where is doesn't have a value to output I get #VALUE! then the 2nd query output.

Thank you for any help.

enter image description here

CodePudding user response：

Try

=QUERY(B2:D41, "SELECT B,C,D WHERE C is not null ", 0)

CodePudding user response：

When you combine two arrays vertically using an { array expression }, both arrays must be the same width.

Similarly, when you combine two arrays horizontally using an { array expression }, both arrays must be the same height.

To avoid the issue, use this pattern:

=arrayformula( 
  { 
    query(B2:D41, "where D <> '' ", 0); 
    query( { B42:B54, iferror(C42:D54 / 0) }, "where Col1 <> '' ", 0) 
  } 
)

CodePudding user response：

the first query has 3 columns and the second query has 1 column - that's the reason why you getting that array error because you are trying to stack 3-columned array on top of 1-columned array

if your intention was to put it next to each other formula should be:

={QUERY(B2:D41,  "SELECT B,C,D WHERE D <> ''", 0), 
  QUERY(B42:B81, "SELECT B WHERE B <> ''", 0)}

if your intention was to put it under each other formula should be:

={QUERY(B2:D41,  "SELECT B,C,D WHERE D <> ''", 0); 
  QUERY(B42:B54, "SELECT B,' ','  ' WHERE B <> '' label ' ''', '  '''", 0)}