...

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


 
 •  106 View  •