Grabbing only non-empty cells from a row range for every row

Question

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!

Answer 1

• the part you are interested in is:

=ARRAYFORMULA(REGEXREPLACE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF('Raw Data'!AL3:AY8<>"", 
 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,
 REGEXREPLACE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF('Raw Data'!AL3:AY<>"", 
 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))), ), "♣")),
 ARRAY_CONSTRAIN(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))), ), "♣")),
 COUNTBLANK('Raw Data'!A1:A)+COUNTA('Raw Data'!A1:A)-1, )), 'Raw Data'!AZ3:AZ}, )), "where Col1 !=''")}}

• demo spreadsheet