Filtering multiple columns in Google Sheets without blanks
So basically, I have a google sheets sheet that has form answers, but depending on what they answered in the google forms they'll answer different questions. So I basically have a filter function that filters these answers and sorts them by category in my google sheets. For example the answers in the E row (see image 1 as reference) are sorted on a different sheet (see image 2 as reference) https://i.sstatic.net/UsAqJ.png [Image 1]. https://i.sstatic.net/WGr0w.png [Image 2]. As you can see the cells are copied under their category.
But for answer in the form I have multiple columns in the sheet that these answers need to be put in. The answers I need to be stored are located in the rows R, S, W, Z. (see image 3 as reference). https://i.sstatic.net/1ck2E.png [Image 3]. These answers from image 3 need to be stored the same way as the situation describes as above. They need to be stored in the row showing in image 4. https://i.sstatic.net/3gnm7.png [Image 4].
The formula I use to get the answers from 1 row is. FILTER(Antwoorden!R2:R, NOT(ISBLANK(Antwoorden!R2:R))) As this formula gets me the answer from 1 column without the blanks in between. I just need the exact 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 am tried to describe my situation as best as I can as 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 \
