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:
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.
