I got responses from Google form into Google Spreadsheet. I want to filter out the "Weekend" and "Workday". For the "Weekend" days, I only want to select the columns that are related to "Weekend", because of the different sections and conditions on Google Form, if the person selects "Weekend", it will not ask the question "Did you sign in?" "Date" and "Did you check emails", now I want to exact weekend to another sheet, and select the columns that have questions only related with weekends. Here is my sheet
My idea to do this is select rows that are "Weekend" on column D then put them into another sheet, How to select the columns only has "Weekend", which looks like this. How can I only select the column that has value so that I select it doesn't need to select those blank questions?
you can try this.
=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(QUERY(SUBSTITUTE(TRIM(Data!A:J)," ","~"),"Where Col4 = 'Weekend'",1),,9^9)))," ","|"),"~"," "),"Select Col1 where "&"Col1 <>'"&TEXTJOIN("' and Col1 <> '",true,trim(Data!1:1))&"'"),"|")))
here is the link of sample sheet for you reference.