...

Re-categorize or sort the categorized data when it is put into columns

 

Example

There is the data shown in the figure below in the Excel file book1.xlsx, and items with the same Name may have multiple colors.

The data is presented in the form of the figure below, one row for each Type, followed by each Name and its Color.

Write SPL script:

A

1

=clipboard().import@t()

2

=A1.group(Type).(~.group(Name))

3

=A2.((~.Type|~.(Name|~.(Color))).conj@r())

4

=clipboard(A3.(~.concat("\t")).concat("\n"))

A1 Read data from the clipboard, the option @t means that the first row is the column name

A2 First group by Type, and then group by Name in the group

A3 Loop through each group of Type, first take Type, and add the sequence composed of Name and Color of each group in its Name subgroup, and finally this sequence is summed with conj. Option @r means recursive calculation until all members are no longer a sequence

A4 Concatenate the internal data of each Type group in A3 with \t and concatenate with \n between groups to form text and put it into the clipboard 

First open the file book1.xlsx, select data A1:C6, and then press Ctrl+C to copy to the clipboard. Go back to esProc to run the SPL script, and then go back to the Excel file after the run is over, click E1, Ctrl+V to paste the calculation results.

 
 •  344 View  •