Please see below the project I'm trying to solve and for which I need your help.
| (E) Choice-not sorted | (F) Choice-sorted |
|---|---|
| Banana, Orange, Apple | Apple, Banana, Orange |
| Strawberry, Orange | Orange, Strawberry |
| Coffee, Apple, Strawberry, Lime, Shoes | Apple, Coffee, Lime, Shoes, Strawberry |
| Etc. | Etc. |
How to get F from E?
I found some solutions in stackoverflow but for some reasons, I didn't manage to make them work.
Thank you very much!
CodePudding user response：
pivot it:
=ARRAYFORMULA(IFNA(VLOOKUP(ROW(A1:A5)&"", REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(
QUERY(TRIM(SPLIT(FLATTEN(ROW(A1:A5)&"♦♣"&SPLIT(A1:A5, ",")&","), "♣")),
"select max(Col2) where Col2 <>',' group by Col2 pivot Col1"),,9^9)), "♦")), ",$", ), 2, )))
