Problem with Query function - How to fix it? | Google Sheets

Question

I have a function that creates several rows from one, but the results are not displayed correctly, i.e. the values "No" ​​that should be displayed in the F column are displayed in the E column if there any cell is empty. How to fix it?

https://docs.google.com/spreadsheets/d/1aI0o6XL7_Z0SAS-S2GQeLYRL4y72gKGoH9INRxDJeWA/edit?usp=sharing

Answer 1

You just needed to add parameters 1,0 to the SPLIT clause:

=ArrayFormula(QUERY(SPLIT(flatten(A2:A10&","&B2:B10&","&C2:C10&","&split(D2:D10,", ")&","&iferror(REGEXREPLACE(F2:F10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1"),"null")&","&iferror(REGEXREPLACE(E2:E10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1"),"null")),",",1,0),"where Col4 <> 'null' and Col4 is not null"))

However, in addition to your posted problem, I see you also have a repeated item at the bottom of the list with empty final columns. So try this version to correct both problems:

=ArrayFormula(QUERY(TRIM(SPLIT(flatten(A2:A10&","&B2:B10&","&C2:C10&","&split(D2:D10,", ")&","&iferror(REGEXREPLACE(F2:F10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1"),"null")&","&iferror(REGEXREPLACE(E2:E10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1"),"null")),",",1,0)),"Select * Where Col6 Is Not Null and Col6 <> 'null'"))

(See my added sheet, "Erik Help")

Answer 2

This happens because of the blank nicknames in column F. Since you are only splitting a flattened values, a null will lead to your split formula to adjust and give you a result you are not expecting. As a workaround, you can add a space in your replacement text in REPLACEREGEX formula. Instead of "$1", you can use "$1 " but note that this will add space even if the nicknames are not null.

See modified formula below and screenshot of the result:

=ArrayFormula(QUERY(SPLIT(flatten(A2:A10&","&B2:B10&","&C2:C10&","&split(D2:D10,", ")&","&iferror(REGEXREPLACE(F2:F10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1 "),"null")&","&iferror(REGEXREPLACE(E2:E10,".*(?:"&split(D2:D10,", ")&"+ - )([^,]+).*|.+","$1"),"null")),","),"where Col4 <> 'null' and Col4 is not null"))

Please let me know if you are good with this workaround.