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

In Excel, Expand All Combinations of Multiple Columns

Subscribe to my newsletter

esProc Desktop

esProc Desktop