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
are sorted on a different sheet.
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
).
These answers need to be stored in the column shown below.
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).
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 \