Data Formulation

Question

I am hoping to get some help formulating an array to more concisely view my data coming in from a google form. Is this something someone can help me with? I'm slightly familiar, but the function to do this is going to be quite complicated, I'm afraid.

On my Google form, I have a question that people may or may not fill out multiple times. In order to let them do that, I've had to copy the question repeatedly on the form. If they need to keep answering the question, they continue down the copied versions. If they're done, they select "no," and are directed to finish the form.

On my intake spreadsheet, this data comes through as about 30 different columns to account for each of the copied versions of that question. I'm hoping to order the columns that are filled out and then delete columns if they are left blank. It would really help the readability of this spreadsheet.

Here is a copy of the Sheet: https://docs.google.com/spreadsheets/d/1_W8PpwiMa60sPY8YF2pTEx-QPIMexqs5ZizVPiWgxMQ/edit?usp=sharing

Answer 1

try:

=ARRAYFORMULA(QUERY(SUBSTITUTE(SPLIT(FLATTEN(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!A2:C="",,"¤"&TO_TEXT('Form Responses 1'!A2:C)), " ", "×")),,9^9))&FILTER(SUBSTITUTE(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), " ", "×"), 
 MOD(COLUMN('Form Responses 1'!D2:AT), 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-1, 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-2, 4)=0)&FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!AV2:BA="",,"¤"&TO_TEXT('Form Responses 1'!AV2:BA)), 
 " ", "×")),,9^9))), "¤"), "×", " "), "where not Col4 starts with ' '"))

with conditional formatting:

=INDEX(COUNTIFS($A:$A, $A1, ROW(A:A), "<="&ROW(A1)))>1

demo sheet

---

update:

=ARRAYFORMULA(QUERY(SUBSTITUTE(SPLIT(FLATTEN(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!A2:C="",,"¤"&TO_TEXT('Form Responses 1'!A2:C)), " ", "×")),,9^9))&FILTER(SUBSTITUTE(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), " ", "×"), 
 MOD(COLUMN('Form Responses 1'!D2:AT), 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-1, 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-2, 4)=0)&FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!AV2:BA="","¤ ","¤"&TO_TEXT('Form Responses 1'!AV2:BA)), 
 " ", "×")),,9^9))), "¤"), "×", " "), "where not Col4 starts with ' '"))