CODEWITHSUNDEEP
sqlif-statementgoogle-sheetsarray-formulasgoogle-query-language
Martha
Martha

Reputation: 27

If/Filter clause to query formula (with arrayformula embeded)

I have a response sheet on GSheets with over 100 columns/questions that can either be responded by Yes or No. If the candidate answers no they will have to fill in a freetext question.

The following formula - allows me to export the questions that were answered Yes or No from one sheet to another:

=QUERY('Module 1 Responses'!$A$2:$AS,"SELECT "&join(",",arrayformula(SUBSTITUTE(ADDRESS(1,MATCH($A$1:$W$1,'Module 1 Responses'!$A$1:$AS$1,0),4),1,""))))

I would like to add an "if" or a "filter" clause to this formula so that I can only see the "No"s or that would convert all the "Yes" values to blank - if at all possible

Please see sheet with mock data here: https://docs.google.com/spreadsheets/d/1VBrkVwBa5jXcrQXwB7QGFJhgcbBfjcmCawV7B1YqPcI/edit?usp=sharing

The tab I am working on is called "Action Plan". Thanks in advance for your help!

Upvotes: 0

Views: 89

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(SUBSTITUTE({'Module 1 Responses'!A:C, FILTER('Module 1 Responses'!D:Z, 
 MOD(COLUMN('Module 1 Responses'!D:Z), 2)=0)}, "Yes", ))

enter image description here

Upvotes: 1

Related Questions