Filtering and exporting data based on condition-CodePudding

I have a responses sheet (synced to a Google Form) which has many columns (over 100). The responses can either be Yes/No and if the response is No - there is a free text response required. I want to separate the No's and the Free-text responses from the rest - ideally in a different sheet. This filtering should be somewhat automated so that each time I get a new response, I do not need to amend the formula, etc. I have tried filtering the data by colour and exporting it via SheetGo - but it only allows me to select one column as a reference and I have more than one. I have tried using a Query/filter function with an IF clause - but I still get a few Yes coming across. Would anyone be able to assist, please? Thanks in advance for your help!

CodePudding user response：

In a new sheet, use the formula:

=filter(FormData!A2:B, FormData!A2:A="No")

Change FormData to the name of your sheet where form responses are entered. Change A2:B with the range of your data that you want filtered out (If the yes/no responses are not in adjacent columns, you can change the filter range to something like {FormData!A2:A, FormData!G2:G}). And finally, change A2:A with the range of the "Yes/No" responses.

Example of all this shown below:

Form Data:

Filter:

Please let me know if you have any questions. Hope this helps!

Edit

Here is an updated answer based on clarifications made in the comments.

Raw Response Sheet:

Filtered Sheet:

Cell A1:
={"User",arrayformula(filter(if((a!$A$2:$L$2="Yes"), offset(a!$A$1:$L$1,0,1), if(a!$A$2:$L$2="No", offset(a!$A$1:$L$1,0,1), "")),if((a!$A$2:$L$2="Yes"),a!$A$1:$L$1, if(a!$A$2:$L$2="No", a!$A$1:$L$1, ""))<>""))}

This formula finds the headers for yes/no questions based on the first set of responses. If a question is answered Yes or No, the header is set. The first header is set to be "User". Please change this to whatever the identifying header is named in your sheet.

Cell A2:
=QUERY(a!$A$2:$L$9,"SELECT "&join(",",arrayformula(SUBSTITUTE(ADDRESS(1,MATCH($A$1:$D$1,a!$A$1:$L$1,0),4),1,""))))

This formula matches the header names with the headers from the raw response sheet, finds the columns, and uses these references to query the results.

For both of these formulas:

Replace all a!$A$2:$L$2 and a!$A$1:$L$1 with the columns that hold your data. There is no need to change the row numbers.

Replace $A$1:$D$1 (in the second formula) with the top row of your filtered sheet. This references the headers of the Yes/No questions and user identifiers.
Replace all a! references with the name of your raw response sheet (ie [nameOfSheet]!).

I broke these formulas apart to help keep things neater and more understandable. Here is a single formula solution that can be placed in cell A1 of the filtered sheet:

={"User",arrayformula(filter(if((a!$A$2:$L$2="Yes"), offset(a!$A$1:$L$1,0,1), if(a!$A$2:$L$2="No", offset(a!$A$1:$L$1,0,1), "")),if((a!$A$2:$L$2="Yes"),a!$A$1:$L$1, if(a!$A$2:$L$2="No", a!$A$1:$L$1, ""))<>""));QUERY(a!$A$2:$L$9,"SELECT "&join(",",arrayformula(SUBSTITUTE(ADDRESS(1,MATCH({"User",arrayformula(filter(if((a!$A$2:$L$2="Yes"), offset(a!$A$1:$L$1,0,1), if(a!$A$2:$L$2="No", offset(a!$A$1:$L$1,0,1), "")),if((a!$A$2:$L$2="Yes"),a!$A$1:$L$1, if(a!$A$2:$L$2="No", a!$A$1:$L$1, ""))<>""))},a!$A$1:$L$1,0),4),1,""))))}

This formula would need to also be edited as listed out above, which can be trickier given how jumbled this formula appears. A simple find and replace can work wonders here.