Searching for a Arrayformula that looks up and joins header titles

1
votes

I have a formula that joins header titles (form questions) in a Google Form response sheet. The problem I have is that the range in the IF statement is not updated when new rows are entering. It keeps joining the results from row 2 for all subsequent records.

The arrayformula I'm using:

={"Beroepen";ArrayFormula(if(len(B2:B);TEXTJOIN(" -- "; TRUE; IF("ja"=EE2:GR2; $EE$1:$GR$1; ""));""))}

(this basically checks if cell value equals 'ja' and then joins the header title like 'titleA -- titleC --...' in a new cell)

Following arrayformula works, but it does not get copied when new records are coming in:

=ArrayFormula(TEXTJOIN(" -- "; TRUE; IF("ja"=EE2:GR2; $EE$1:$GR$1; ""))

... since a PDF is generated manual 'pulldown' is not possible every time answers are coming in.

Please see example sheet:

https://docs.google.com/spreadsheets/d/1My9HVswquZwh5WNHZ8656xaIGwUdG2pZukN-BiDbBIQ/edit?usp=sharing

1
google-sheetsarray-formulasgoogle-forms

1 Answers

3
votes

In JJ1 heb ik deze formule toegevoegd

={"Beroepen"; ArrayFormula(if(len(A2:A); substitute(regexreplace(transpose(query(transpose(if(EE2:GR="ja"; substitute(EE1:GR1; " "; "_");));;rows(A2:A))); "\s{1,}"; " -- "); "_"; " ");))}

See if that works?