Arrange Google Form Checkbox Responses into a List

Question

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.

Answer 1

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<>''"))

Spreadsheet solution: HERE

Answer 2

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.