arraysgoogle-sheetsfiltergoogle-sheets-formulagoogle-query-language

Filtering multiple columns in Google Sheets without blanks


So basically, I have a google sheets sheet that has form answers. Depending on the answer in the google forms people will see different questions. So I have a filter function that filters these answers and sorts them by category in my google sheets.

For example the answers in the Col E Image 1 are sorted on a different sheet. Image 2

As you can see the cells are copied under their category.

But for questions in the form, I have multiple columns that contain the answers (Cols - R, S, W, Z). Image 3 These answers need to be stored in the column shown below. enter image description here

The formula I use to get the answers from a column is

FILTER(Antwoorden!R2:R, NOT(ISBLANK(Antwoorden!R2:R)))

This formula gets me the answer from the column without the blanks in between. I need the same thing, but with multiple columns that work in order from how they are located in the sheet.

(Sorry if the thread is vague, I tried to describe my situation the best I could, but I am not a native English speaker. I apologize).


Solution

  • this is usually done like this: you take static columns (like your column A) and join it with some unique symbol (let's say ♦) and then you take your dynamic range (like your R,S,W,Z columns) next, you flatten it, split it by that unique symbol and remove blanks:

    =INDEX(QUERY(SPLIT(FLATTEN(Antwoorden!A2:A&"♦"&{Antwoorden!R2:S, Antwoorden!W2:W, Antwoorden!Z2:Z}), "♦"), 
     "where Col2 is not null", ))
    

    if you are on different locale change , to ; and , inside {} to \

    enter image description here