I am collecting data on a Google Form where users select a name then answer specific questions depending on what name they select. This leaves the resulting response spreadsheet with many blank columns so I am trying to filter/consolidate the responses for each name into its own tab. For example, I took the responses for Cameron A. and tried to filter them into a tab called "CA". I used the FILTER function and then an array formula so it would populate as new data came in, but the two sets of data do not align.

Demo spreadsheet

  • The filter formula works fine. It will auto-update when the form receives new data. Why don't you use it for all columns: =FILTER('Form Responses 2'!C2:T, SEARCH("Cameron Axelrod", 'Form Responses 2'!C2:C)) – user79865 Nov 19 '15 at 0:45
  • thank you. filtering across all columns would work for Cameron, but not in creating other tabs for other names since the data is spread out for other names. i don't know how to filter across two ranges, i guess. I created a tab for Emily (EB). maybe I'm missing something. – Bardo Nov 19 '15 at 1:22
  • I think that instead of having 173 columns for each person, you want only a few, those that actually have responses. In other words, you want to get rid of the blanks columns, right? – Rubén Nov 19 '15 at 1:34
  • Yes, and I tried that already but (let me try to explain this) there are some questions for each person that are left blank and those would be sorted out, thus offsetting the answers. I would like to specify the range for each person to sort rather than just remove blank columns. – Bardo Nov 19 '15 at 1:42
  • I filtered data to the far right end of the response sheet, but items do not align (since some follow-up questions are optional). In addition, the headers for each column vary depending on the student. Thus the need to just filter out two specific ranges for each student. – Bardo Nov 19 '15 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.