2

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.stack.imgur.com/UsAqJ.png [Image 1]. https://i.stack.imgur.com/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.stack.imgur.com/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.stack.imgur.com/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).

player0
  • 124,011
  • 12
  • 67
  • 124
TWVD
  • 35
  • 4

1 Answers1

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • I'm confused, here is the spreadsheet if you would like to show it to me in there. https://docs.google.com/spreadsheets/d/1Gij2R7BbPFE1qi65q76nEVljAZgZAWiCKAa7DAHhJq0/edit?usp=sharing – TWVD Feb 24 '22 at 23:41
  • @TWVD sure, see the green cell in your sheet – player0 Feb 24 '22 at 23:49