How to List All Permutations of Members from Multiple Sets

 

Task description

Excel file book1.xlsx contains three cells. Each cell contains data separated by comma.

undefined

We want to list all possible permutations of members from all the three sets. Each permutation occupies a row, as shown below:

undefined

 

Directions:

1.  Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Write script in esProc:

undefined

List the code separately for an easy viewing:

A

1

=clipboard().split("\t").(~.split@c())

2

=A1.("A1("/#/")").concat(";")

3

=xjoin(${A2})

4

=clipboard(A3.export())

A1   Import data from clipboard, split each row with \t and then each value with comma; @c option enables splitting data by comma.

A2   Loop through each member of A1’s sequence to generate a string in the format of A1(1);A1(2);A1(3).

A3   Use A2’s string as xjoin() function’s parameter in the form of macro. That is, perform  cross product over the three split substrings.

A4   Convert A3’s result into a string and export it to clipboard.

 

3Switch back to Excel, select data area A1:C1, and press Ctrl+C to copy it to clipboard.

In esProc, press F9 to execute the program, and switch back to Excel after the execution is finished to click A2, and press Ctrl+V to paste the result in.