Generate permutations and combinations

 

Example

There are 3 cells in the Excel file book1.xlsx as shown in the figure below, the data in the cells are separated by commas.

List their permutations and combinations, one combination per row, as shown in the following figure:

Write SPL script:


A

1

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

2

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

3

=xjoin(${A2})

4

=clipboard(A3.export())

A1 Read data from the clipboard, use \t to split the data in each cell, and then split the data in each cell with a comma, the option @c means split with comma 

A2 Loop each member of A1 to form a text A1(1);A1(2);A1(3) 

A3 Concatenate A2 into text and convert it into a parameter of the xjoin function in the form of a macro. That is, the sequence formed by splitting the data in each cell is completely cross-multiplied. 

A4 Convert the calculation result in A3 into text and put it into the clipboard 

First open the book1.xlsx file, select the data A1:C1, and then press Ctrl+C to copy to the clipboard. Run the SPL script in esProc, return to the Excel file after the execution is complete, select A2, and press Ctrl+V to paste the calculation results in the clipboard.