Extracting a list of values based on a multiple criteria

[kylerisi]

Hi team.

We have a survey which asks our customers a series of questions in the form of radio options. At the end of the survey the customer has the option to input a free text response. This data exists as RAW CSV data and I am trying to extract all the user's free text answers from the spreadsheet. The below formula scans column E in Sheet 'Surveys All'!E:E for any values that DO NOT CONTAIN the below values (these are not free text answers)

• Very
• Good
• Quite
• Not Very
• No
• Meh
• Yes
• Suggest a Quide

If it finds a match that does not contain the above, then the formula returns the name of the survey which is found in 'Surveys All'!C:C.

The array formula is then dragged down and all the names of the surveys that contain free text answers are then listed.

=ArrayFormula(IFERROR(INDEX('Surveys All'!C:C,SMALL(IF(LEFT('Surveys All'!$E:$E,5)<>"Very",IF(LEFT('Surveys All'!$E:$E,4)<>"Good",IF(LEFT('Surveys All'!$E:$E,5)<>"Quite",IF(LEFT('Surveys All'!$E:$E,8)<>"Not Very",IF(LEFT('Surveys All'!$E:$E,3)<>"No",IF(LEFT('Surveys All'!$E:$E,3)<>"Meh",IF(LEFT('Surveys All'!$E:$E,3)<>"Yes",IF(LEFT('Surveys All'!$C:$C,15)<>"Suggest a Guide",IF('Surveys All'!$E:$E<>"",ROW('Surveys All'!$E:$E)))))))))),ROW(2:2))),""))


However I need to make a small adjustment to my formula.

Instead of listing all surveys that contain free text answers, I only need the formula to list the surveys titles that match the title in A1


I.e: I need the formula to scan 'Surveys All'!C:C first and find a survey title that matches the title in A1. if it's a match then check to see if the value in collumn 'Surveys All'!E:E DOES NOT CONTAIN

• Very
• Good
• Quite
• Not Very
• No
• Meh
• Yes
• Suggest a Quide

If it doesn't then return the name of the survey in 'Surveys All'!C:C.

If anyone can help me make this adjustment it would be a massive help

Anything is appreciated.

Thank you

 

[istiasztalos]

Hi,

Are you using Google Spreadsheet? your formula formatting and beginning with ArrayFormula seems to indicate so

 

[istiasztalos]

Hi,

If so, it would be easier to use the query formula

=QUERY(Surveys All'!C:E;"select C where C = '"&A1&"' AND NOT E contains 'Very' AND NOT E contains 'Good'")

Just continue repeating the "AND NOT E contains '...'" parts.

If you are in Excel, using a pivot table would be far easier, is that not an option?