0

I am trying to stack vertically the results of these two QUERY functions:

QUERY(FILTER('Sheet1'!U8:AB;'Sheet1'!Z8:Z<>"N/A";'Sheet1'!Z8:Z<>"");"SELECT Col6, Col1";0) QUERY(FILTER('Sheet1'!AD8:AK;'Sheet1'!AH8:AH<>"N/A";'Sheet1'!AH8:AH<>"");"SELECT Col6, Col1";0)

(Separately, both QUERY worked correctly)

But I get the error message:

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

Following the solution shown in:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows - indirect formula error in google spreadsheet

I tried using using this formula:

={QUERY(FILTER('Sheet1'!U8:AB;'Sheet1'!Z8:Z<>"N/A";'Sheet1'!Z8:Z<>"");"SELECT Col6, Col1";0);QUERY(FILTER('Sheet1'!AD8:AK;'Sheet1'!AH8:AH<>"N/A";'Sheet1'!AH8:AH<>"");"SELECT Col6, Col1";0)}

Obviously, both QUERY functions have a 2-column result. Of course, they may not have the same number of rows.

I also tried the solution shown in:

How to combine 2 Google Sheets Query tables chronologically by Date?

By using:

=QUERY({FILTER('Sheet1'!U8:AB;'Sheet1'!Z8:Z<>"N/A";'Sheet1'!Z8:Z<>"");FILTER('Sheet1'!AD8:AK;'Sheet1'!AH8:AH<>"N/A";'Sheet1'!AH8:AH<>"")};"SELECT Col6, Col1";0)

In both instances I keep getting the same error message.

Where is the error?

Thank you.

JCAN
  • 3
  • 1

1 Answers1

0

Can you test this to troubleshoot and see if you are able to populate something.

={iferror(QUERY(FILTER('Sheet1'!U8:AB;'Sheet1'!Z8:Z<>"N/A";'Sheet1'!Z8:Z<>"");"SELECT Col6, Col1";0);{""\""});
  iferror(QUERY(FILTER('Sheet1'!AD8:AK;'Sheet1'!AH8:AH<>"N/A";'Sheet1'!AH8:AH<>"");"SELECT Col6, Col1";0);{""\""})}
  • If only one query results populate then most likely the scenario is that the second query has no results or it could be the spotty one that ought to be fixed.
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • It works! Yes, there was no result for the second QUERY. Thank you. – JCAN Feb 21 '23 at 19:21
  • The previous formula worked perfectly for my previous case. Now the error message is back again, when I tried: ={SI.ERROR(QUERY(FILTER('Sheet1'!AZ9:BD;'Sheet1'!AZ9:AZ=F2);"SELECT Col2, Col3, Col1, Col4, Col5";0);{""\""});SI.ERROR(QUERY(FILTER('Sheet1'!BF9:BJ;'Sheet1'!BF9:BF=F2);"SELECT Col2, Col3, Col1, Col4, Col5";0);{""\""})} If I use only one QUERY, the results are correct, but if I try to combine both, the error appears again: "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows" – JCAN Feb 22 '23 at 04:24
  • Use `{""\""\""\""\""}`; that's 5 header spaces instead of 2 and should be equal to the number of columns you are displaying within your query `Col2, Col3, Col1, Col4, Col5` – rockinfreakshow Feb 22 '23 at 09:03
  • you seem to have 5 columns but 6 blank headers! – rockinfreakshow Feb 22 '23 at 22:11
  • True. It now works. Sorry for the confusion. Thank you! – JCAN Feb 22 '23 at 23:03