7

I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G.

I have a much more complicated formula doing the same thing but more variations, columns D(blank or not) - E and F(blank or not) - G

The following formula works

=SORT(ArrayFormula({
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA,
  "select A, B, C, D, F, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, E, F, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, D, G, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, E, G, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NULL AND F IS NULL order by F desc"))
 }),5,FALSE)

But my more simple version of the same formula returns an error

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL")); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"))
 }),7,FALSE)

The error is: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

A link to a copy of the google sheets is as follows:

https://docs.google.com/spreadsheets/d/1s6spmfCVP6P331Zr9xfE9kiei3Wly6FRQCdA8Y3e7tA/edit?usp=drivesdk.


Additional Question

UPDATED FORMULA of the original working formula

=SORT( ArrayFormula( { IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, F, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, F, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, G, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, G, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NULL order by F desc"),{"","","","","","","",""}) } ) ,5,FALSE,4,FALSE)

WORK AROUND

In the 6 Star Gear Sets (Hidden) sheet the formula references, there is a row with A column "zzzzz" and both F and G is blank. When this row is called using the above ArrayFormula, I use a Conditional Format to hide all rows with "zzzzz".

QUESTION

If I remove the row with "zzzzz" in column A from the hidden sheet, I recieve an error.. why?

ADDITIONAL INFO

Breaking the formula into individual QUERY forumlas and removing IFERROR, shows the 3rd query is empty and returns #N/A error, but the 4th query also does not return any results and it does not give such an error. I figured the IFERROR statement would handle this but to no avail.

UPDATE

Found the 4th query isn't returning empty. It is actually returning blank cells. Which again, adds to the list of questions. Because I have the IFERROR function removed and where A != ' ' I thought would handle this. Is that not Where A DOES NOT MATCH blank?

I've tried wrapping it in an IF(IFNA()) formula but I guess you can't use IFNA outside Conditional Formatting because it says IFNA() isn't a known function.

UPDATE 2

For some reason I immediately forgot the part about the number of blank arguments in the IFERROR statement needs to match the number of columns being referenced.

However, =SORT is no longer working correctly as there is now a blank row at the top of the new table and I still do not have a good explanation of why the 4th Query is filling the table with blank rows.

UPDATE 3

In the A column of my "6 Star Gear Sets" sheet, I have a formula that numbers everything in the order they appear unless the B column is blank. For some reason, the blank row that is inserted due to the =IFERROR(,) isn't actually being treated as being blank.

In additional, my =SORT orders everything in descending order by the F and then E column. However, it treats the F and E in the inserted blank row as the highest value row.

UPDATED FORMULA

=SORT( ArrayFormula( { IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, F, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, F, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, G, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, G, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NULL order by F desc"),{"","","","","","","","","","","",""}) } ) ,5,FALSE,4,FALSE)

UPDATED LINK

Below is an updated copy of my spreadsheet.

https://docs.google.com/spreadsheets/d/17Ev1_Scobnl16H9TuTrHK_es7fZGkMqHyvmpWWWVbow/edit?usp=drivesdk

3
  • 1
    Suggestion both for posting and your own work: use linebreaks in complex formulas. They are allowed, and the formula box can be stretched vertically to see the entire formula.
    – user135384
    Jan 2, 2018 at 15:57
  • @CrazyIvan difficult for me to work with. I have only a notepad app on andriod tablet. I wish I had home and end buttons. No PC :( but I will try harder for posting, your edits look better. Jan 2, 2018 at 20:29
  • @CrazyIvan Should I start a new question for the remaining problems/questions? Jan 3, 2018 at 20:01

2 Answers 2

7

When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.

Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.

2
  • You are both helpful and informative. I am a little emberassed to say how many hours I spent on that error so I won't say. But asking the question yielded more than just the solution! '=SORT(ArrayFormula({IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, "select A, B, C, D, E, F, H, I where A != ' ' AND F IS NOT NULL"),{"","","","","","","",""}); IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, "select A, B, C, D, E, G, H, I where A != ' ' AND F IS NULL"),{"","","","","","","",""})}),6,FALSE,7,FALSE)' is my new formula. Jan 2, 2018 at 18:49
  • Although you fixed my original question, I went ahead and asked a second (not sure if I should have started a whole new question on stack exchange since it is so closely related to the original question and your comments.) I did as you say and tried breaking up the formula to the queries only but that left more questions than answers, the 3rd query that is responsible for zzzzz gives an #N/A error form empty query, but the 4th query does not give this error. The 5 Star sheet does not have this problem at all. Jan 2, 2018 at 21:23
1

INTRODUCTION

I have solved the issues that are found in UPDATE 1 thru 3 in the original question. However, I feel the formula is a little convoluted and that there should be a much simpler answer.

EXPLANATION

I added both an IF and INDEX function to the formula that solves the previous formula adding to many blank rows as well as non-true blank rows that causes the problem with sorting and other formulas checking if the non-true blank row is actually blank. I added 1 IF and INDEX function for each QUERY.

I also changed the IFERROR statements from creating blank cells but instead, referencing cells that are true blanks.

The IF statement uses a portion of the original formula to check the results of the QUERY formula. The INDEX function limits the check to only the first row. The Query Check is then compared to 12 blank columns and a single row.

IF TRUE: It simply references a true blank row instead of creating its own blank rows, the creation of blank rows ended up not being true blank rows, causing all the issues before.

IF FALSE: It runs the original formula, adding to the table the appropriate cells from the reference sheet.

SIDE EFFECT

This is not an issue for my sheets, but someone trying to replicate a similar formula may notice that each time the query adds a true blank row you will be unable to add anything in these cells because this formula will throw an error for trying to write the blank cell into that portion of the table.

I tried using the FILTER function to only return the number of rows that actually contain data in the referenced sheet but this caused a lot of EXPECTED errors.

UPDATED FORMULA

=SORT(
ArrayFormula(
    {
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, D, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NOT NULL AND F IS NOT NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, D, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NOT NULL AND F IS NOT NULL"
            )
        ); 
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NOT NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NOT NULL"
            )
        ); 
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NULL"
            )
        );
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, D, G, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NOT NULL AND F IS NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, D, G, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NOT NULL AND F IS NULL"
            )
        );
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, G, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NULL"
                    ),
                    1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, G, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NULL"
            )
        )
    }
),
5,FALSE,4,FALSE,3,FALSE
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.