How to split cell data separated by commas and transfer to another sheet? How to automate this?

Question

Google Sheets

Hello, I ran into some difficulties. Created a Google Form with data submission to Google Sheets. After filling out the form, it looks like this:

Picture 1

I installed the Form Mule extension to send out mailings to certain groups of mail (I made a kind of filter by direction):

Picture 2

Realized that in this way letters are sent only when I choose only one direction (what I need: when a person chooses 3 directions (or any other number) so that letters are sent using 3 Form Mule templates.
How I see the solution to the problem with mine " filters": make another sheet in the same table, where the already formatted data will be transferred, and so that it looks something like this (using the example of picture 1):

Picture 3

But now the third problem pops up: I don't know how to do it :) And it will also affect the size of the table, but that's okay.
I will be glad to your thoughts and suggestions. Maybe you know how else you can make a mailing of such a plan? Oh, and yes, sorry for the obscure title.

Answer 1

I recommend the script.
In general, this problem can be solved with formulas, but "transfer" here will be conditional, only by reference, i.e. data can "float", and this will have to be dealt with somehow.
First, to the right of your data, you need to break the column in which the enumerated data is located. Please note that there can be many of these columns (as many as you have unique values, in your case directions).

Further, already on another sheet, you create an array of arrays of all options and filter it. The number of arrays depends on the number of unique values ​​that can be obtained by iterating over all the values ​​in the cells to split. And this is the most difficult task. If you have 20 or more values, then the formula can become monstrous. In this case, there are only three values ​​"Apples, Pears,

Spreadsheet with example https://docs.google.com/spreadsheets/d/1hmbgD-nW7w...