I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data.

One of the things I need to do is: from a range of columns in one row, pull the non-empty cells--and do this automatically with every new response that comes in.

This is what I've come up with that works with a single row, but I can't figure out how to have it automatically apply to the entire column:

=transpose(QUERY(unique(transpose({'Raw Data'!AL3:AY3,'Raw Data'!AL3:AY3})),
 "SELECT Col1 WHERE Col1 <> '' ORDER BY Col1 ASC LIMIT 8",0))

Because the survey will insert rows, it ends up adjusting any cell references in my 'Processed Data' sheet, so I've been trying to use ARRAYFORMULA unsuccessfully.

Also, the range of columns will be more than 400 (options of schools), so manually writing out individual column names would probably be unfeasible.

Here's a link to the spreadsheet

The column of issue starts on column O

Background: In the survey, a respondent can select multiple schools to a maximum of 8 out of a selection of 400+ schools. In the 'Raw Data' sheet, each school option is represented by a column.

Hopefully all of that made sense. Any help would be greatly greatly appreciated - I've been stuck on this for a while!

  • whats the range you want to apply this smush? whole or partial?
    – user0
    Commented Apr 20, 2019 at 15:38
  • 'Raw Data'!AL3:AY3, which is being referenced from 'Processed Data'!O3 So to clarify, I would need to be referencing 'Raw Data'!AL3:AY3 to grab the schools, and show them in 'Processed Data'!O3:V3, but for every single row automatically
    – pos18
    Commented Apr 20, 2019 at 15:48

1 Answer 1

  • the part you are interested in is:

 SUBSTITUTE('Raw Data'!AL3:AY8, " ", "♠"), )), , 999^99)), " "), "♠", " "))


  • but you could have it all in one-cell solution:

={{"Timestamp (mm/dd/yyyy)", "Duplicate", "First Name", "Last Name", "Phone", 
  "Email Address", "Zip Code", "Parent/Guardian", "Teacher", "Administrator",
  "Elected Official", "Community Member", "Business Organization", "Non-Profit Organization",
  "Affiliated_School_1", "Affiliated_School_2", "Affiliated_School_3", "Affiliated_School_4", 
  "Affiliated_School_5", "Affiliated_School_6", "Affiliated_School_7", "Affiliated_School_8",
  "BeCampusRep"}; {QUERY(ARRAYFORMULA(IF(LEN('Raw Data'!A3:A), {'Raw Data'!C3:G, 'Raw Data'!AC3:AK,
 SUBSTITUTE('Raw Data'!AL3:AY, " ", "♠"), )), , 999^99)), " "), "♠", " "), IF(
 MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))>0,
 IFERROR(SPLIT(IF(LEN('Raw Data'!A3:A), REPT(" ♣", 
 MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))), ), "♣")),
 MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))), ), "♣")),
 COUNTBLANK('Raw Data'!A1:A)+COUNTA('Raw Data'!A1:A)-1, )), 'Raw Data'!AZ3:AZ}, )), "where Col1 !=''")}}


  • Thank you so much! This worked wonderfully.
    – pos18
    Commented Apr 21, 2019 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.