1

I have a google form where multiple students will anonymously submit comments/feedback for another peer.

Currently I am using the formula below to essentially concatenate all of the student comments specific to the individual receiving feedback.

 =(join(" 

 ",transpose(iferror(filter($L$3:$L, $C$3:$C=$C3,$B$3:$B=$B3)))))
  • The line break within the "join" function is to separate the individual comments by a line break.
  • Column L: The original comment submitted by the students.
  • Column C: Filters by the name of the student being evaluated.
  • Column B: Secondary filter criteria.

I am currently using the add-on "CopyDown" to apply said formula to each new submission. However, I notice that the newer submissions do not concatenate prior entries even though the filter conditions are met; the formula works correctly when I manually copy/paste it into the cell.

Is there a way to alter the original formula to dynamically update the cell contents as new submissions are incoming? Perhaps through arrayformula?

  • share a copy of your sheet if you are looking for a solution. the formula you use can't be redone into ArrayFormula but there are always ways how to accomplish it with different sets of formulae – user0 Jun 15 '19 at 10:25
1

Functions like JOIN and FILTER can't be used in an array formula (ARRAYFORMULA function ) the way you are thinking (hope?).

A formula using & (concatenate operator) could be used instead of JOIN but I don't think that could be possible to effectively replace FILTER by an ARRAYFORMULA "supported" function, so I think that you best bet is to use Google Apps Script. If you are't familiar with it, please read https://developers.google.com/apps-script/guides/sheets.

Depending on your workflow, you should use an on form submit event, a time driven trigger or execute it manually.

|improve this answer|||||
  • Thank you, I'll check it out. – Al1388 Jun 14 '19 at 19:05

Your Answer

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

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