google-sheetsgoogle-apps-scriptgoogle-sheets-formulapandas-explodefirst-normal-form

xplode data in google sheet

I got a table like this

enter image description here

Category is a multiple selection, the count simply is the number of occurrences. I want to tranform to this table

enter image description here.

In python, i can use xplode function. But u want using formular or appscript in googlesheets to solve this problem. I try arrayformular, and split. But it's not worked

Solution

  • Here's a possible solution:

    =ARRAYFORMULA(QUERY(SPLIT(TOCOL(SPLIT(A2:A4,",")&"|"&B2:B4),"|"),"where Col2 is not null"))

    enter image description here