Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries

Question

enter image description here

I want to change the data from table on the left to table on the right by using formula or function. I already try some formulas but it only work for one column.

All help is welcome! Thank you.

Answer 1

Here's one approach you may test out:

=iferror(reduce(A1:C1,A2:index(A:A,match(,0/(A:A<>""))),lambda(a,c,{a;let(Σ,tocol(split(offset(c,,1),";")),choosecols({chooserows({c,offset(c,,2)},sequence(counta(Σ),1,1,0)),Σ},1,3,2))})))