I'm trying to split a large number of columns in Google Sheets, where some of them include a variable number of comma separated values.
Here's a simplified version of what I'm trying to accomplish.
I want to take a spreadsheet like this one (out of a Google Form):
| Name | Qualities | Colors | Origins |
------------ ---------------------------------------------- ---------------- ----------
| apple | sweet,hard,round, this is my favorite fruit | red, green | gb, us |
| banana | soft,small | yellow, brown | us |
| orange | round,healthy, sour | orange | nl |
| watermelon | round, healthy | green, red | us |
And transform it into this:
| Name | Count |
--------------------------- -------
| apple |1 |
| banana |1 |
| orange |1 |
| watermelon |1 |
--------------------------- -------
| Qualities | |
--------------------------- -------
| round |3 |
| healthy |2 |
| hard |1 |
| small |1 |
| soft |1 |
| sour |1 |
| this is my favorite fruit |1 |
--------------------------- -------
| Colors | |
--------------------------- -------
| red |2 |
| green |2 |
| brown |1 |
| orange |1 |
| yellow |1 |
--------------------------- -------
| Origins | |
--------------------------- -------
| us |3 |
| gb |1 |
| nl |1 |