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.
Add a comment
|
2 Answers
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))})))
Comments
Give a try to this formula-
=CHOOSECOLS(LAMBDA(UnpivotCol,AdjucentCols,QUERY(REDUCE(,REDUCE(,UnpivotCol,LAMBDA(a,x,VSTACK(a,TOCOL(INDEX(JOIN("|",INDEX(AdjucentCols,ROW(x)-(INDEX(ROW(UnpivotCol),1)-1))) & "|" & SPLIT(x,";")))))),LAMBDA(t,g,VSTACK(t,SPLIT(g,"|")))),"offset 2",0))(B2:B4,HSTACK(A2:A4,C2:C4)),1,3,2)
2 Comments
Karina
What is the significance or interpretation of the sequence '1, 3, 2' within the context of the last formula?
Harun24hr
@Karina I build a generic formula to unpivot delimited data. In you case unpivot column is in middle position of dataset. So, get the results column in you desired sequence, I used
1,3,2 of CHOOSECOLS().