Home > Back-end >  Split columns including comma separated values into rows count occurrences of each value (parsing
Split columns including comma separated values into rows count occurrences of each value (parsing

Time:08-25

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      |

Here is the same as enter image description here

  • Related