Error while trying to stack two ranges of data vertically, with the same amount of columns, as a result of two separate QUERY

Question

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.

rockinfreakshow · Accepted Answer · 2023-02-21 06:51:29Z

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.

answered Feb 21, 2023 at 6:51

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JCAN Over a year ago

It works! Yes, there was no result for the second QUERY. Thank you.

JCAN Over a year ago

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"

rockinfreakshow Over a year ago

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 Over a year ago

you seem to have 5 columns but 6 blank headers!

JCAN Over a year ago

True. It now works. Sorry for the confusion. Thank you!

Collectives™ on Stack Overflow

Error while trying to stack two ranges of data vertically, with the same amount of columns, as a result of two separate QUERY

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related