Problem description & analysis:
In the following Excel table, column A contains codes and the other columns are grouping columns having different meanings and containing comma-separated values.
The computing goal: split each grouping column value to generate a row for each unique combination. Below is the expansion result of the first record:
Solution:
Use SPL XLL to enter the following formula:
=spl("=E@b(?.(~.(~.split@c())).conj(eval($[xjoin(] / ~.($[~(] / # / $[)]).concat($[;]) / $[)])))",A2:G4)
As shown in the picture below:
Explanation:
E@b()function converts each row, except for the column header row, to a sequence. split@c splits a string into a comma-separated sequence. conj() function concatenates members of each sequence. eval()function takes the string as the dynamic code to execute. xjoin() performs cross-product on multiple sequences to combine them. $[;] is the simplified form of writing a string, which is equivalent to "";"".
Top comments (2)
Interested in solving such Excel puzzles on your own? Then you shouldn't miss this precious opportunity!
SPL XLL is now FREE to download and apply: scudata.com/download-Desktop.
You can also find other useful information through these links:
🔗Plugin Installation Method:c.scudata.com/article/1652061135502
✨References to other rich Excel operation cases:c.raqsoft.com/article/1651916536524
We also have Discord and Reddit communities where you can find professional Excel help: discord.gg/hgbKEvJ4 & reddit.com/r/esProc_Desktop/. Come and join us right now!🫶🏻