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.
| A | B | C | D | E | F | G | |
| 1 | Assembly# | ProductType | Unit Config | Nominal Capacity | Supply Voltage | Generation | Case Construction |
| 2 | 3H1012290001 | CM | D,P | 24,36 | F | A | A,B |
| 3 | 3H1012290002 | CM | D,P | 48,60 | F | A,B | A,B |
| 4 | 3H1012290003 | CM | D,P | 24,36 | B,C,D,E | A | A,B |
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:
| A | B | C | D | E | F | G | |
| 6 | Assembly# | ProductType | Unit Config | Nominal Capacity | Supply Voltage | Generation | Case Construction |
| 7 | 3H1012290001 | CM | D | 24 | F | A | A |
| 8 | 3H1012290001 | CM | D | 24 | F | A | B |
| 9 | 3H1012290001 | CM | D | 36 | F | A | A |
| 10 | 3H1012290001 | CM | D | 36 | F | A | B |
| 11 | 3H1012290001 | CM | P | 24 | F | A | A |
| 12 | 3H1012290001 | CM | P | 24 | F | A | B |
| 13 | 3H1012290001 | CM | P | 36 | F | A | A |
| 14 | 3H1012290001 | CM | P | 36 | F | A | B |
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 "";"".
SPL XLL is now FREE to download and ready to make your Excel tasks a breeze! Try it now: http://www.scudata.com/download-Desktop