Search code examples
google-sheetsfilterarray-formulastransposegoogle-sheets-query

How to use filter with ArrayFormula?

Im trying to use array formula to list multiple "rows" without the blank cells not from another area in the sheet.

=ArrayFormula(IFERROR(FILTER(GE2:HQ2,LEN(GE2:HQ2)),""))

Also tried

=ArrayFormula(IFERROR(FILTER(GE2:HQ,LEN(GE2:HQ)),""))

Neither returns an array effect. Also tried dragging down the formula to expand the whole sheet. Which works, until a form is submitted. Then the cell in that row loses its formula

Link to an example Sheet

https://docs.google.com/spreadsheets/d/e/2PACX-1vSl8Olx8fYsgROoCU6xLq4M53liT16DgEgtw1RAt0uqpWPosUGZ6aXjBP5UF5pS6y0ZBwAF-8pZrjZR/pubhtml

Any advice would be appreciated

Solution

  • use:

    =INDEX(SPLIT(FLATTEN(QUERY(TRANSPOSE(A2:G4),,9^9)), " "))

    enter image description here

    if dataset contains words in cells use:

    =INDEX(SUBSTITUTE(SPLIT(FLATTEN(QUERY(TRANSPOSE(
 SUBSTITUTE(A2:G4, " ", "♥")),,9^9)), " "), "♥", " "))