0

I am collecting scholarship recipients in a Google form. The recipients names are in a checkbox format, as some donors give to multiple students. I need to separate out the names into additional rows, but keep the corresponding data (amount, name of scholarship, etc.). This form also has student's names so I can't share it to get the specific help. Anyone willing to help me out?

It is a similar issue as found here: Simplifying Google Form Checkbox Responses into Itemized List

I have made a makeshift sheet without sensitive info. Form Responses tab shows how the responses come in, and the Awards by Student tab shows how I need them to be. Find it here.

0

Credits: thanks to player0 for sharing the idea for a different problem

You can try this:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(IFERROR(TEXTJOIN("♦", 1,
SUBSTITUTE(IFERROR(SPLIT(REGEXREPLACE(Responses!B2:B, ", |,", "♥"), "♥")) & "♠"& Responses!$C2:C & "♠"& Responses!$D2:D & "♠"& Responses!$E2:E,
"♠♠", ""))), "♦")), "♠"), "where Col4<>''"))

enter image description here

Spreadsheet solution: HERE

0

You can try out the solution given here: https://www.youtube.com/watch?v=mySZ_eZNmtE It shows a way to use a combination of iserr and search functions without having to deal with the complexities of an array function. And it's easy to customize the result - you can replace the "1" with "Yes" or a tick mark and the "" with a 0 or No or a cross mark. The steps are:

  1. Copy the options exactly as they appear in the google form into a horizontal row. This will basically be the column headers for the individual options, and you get a split-choice matrix.
  2. Use the formula = if(iserr(search(column header-cell containing the option, cell containing the selected options together)),value if option is not found, value if option is found)
  3. Apply the absolute references as needed and just drag the formula over the whole matrix.
1
  • 1
    Welcome to Web Applications. Links only answer might become unhelpful if the linked resource is removed or the link fails. Please add a summary of the suggested solution on the referred resource.
    – Rubén
    Dec 21 '21 at 20:15

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.