RaqForum 41 No.
243 View  • 
     ...

In Excel, expand all combinations of multiple columns

 

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

Use SPL XLL to enter the following formula:

=spl("=E@b(?.(~.(~.split@c())).conj(eval($[xjoin(] / ~.($[~(] / # / $[)]).concat($[;]) / $[)])))",A2:G4)

Picture1png

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 "";"".

Source:https://stackoverflow.com/questions/78330335/uonsolidate-truth-table-in-excel


 
 •  243 View  •