...

Concatenate members with the same ranks

 

Example1

The Excel file book1.xlsx records students' scores, and some of the data is shown in the figure below:

..

The task is to rank students' math scores, concatenate names with the same scores using commas, and the result is shown in the following figure:

..

Write SPL script:

A

1

=T("E:/work/book1.xlsx")

2

=A1.group(Maths).(~.(Name).concat@c()).rvs()

3

=A2.new(#:Rank,~:Name)

4

=T("E:/work/book2.xlsx",A3)

A1 Read the data of book1.xlsx

 

A2 Group the data by Maths, loop through each group, concatenate the names of students in the group with commas to form a string, and finally use the rvs function to sort in reverse order

 

A3 Use A2 to create a new data set, name the row number # as the Rank field, and name the name string of the current row in A2 as the Name field

 

A4 Store A3 to book2.xlsx

 

Example2

The Excel file book1.xlsx records the scores of 4 indicators of some people. Part of the data is shown in the figure below:

..

The task is to fill in the table from column F according to the rule that: fill in the indicator names in turn from the largest index value to smallest one (i.e., in reverse order); if the indicator values are the same, concatenate them with a comma and fill in the same cell, as shown in the following figure:

..

Write SPL script:

A

1

=clipboard().split@n("\t")

2

=A1.to(2,).(~.group@p(~).rvs())

3

=A2.(~.(A1(1)(~).concat@c()))

4

=A3.concat@n("\t")

5

=clipboard(A4)

A1 Read the clipboard string, and split it with \n first, then split with \t, and finally it becomes a sequence of sequences

 

A2 Starting from the second row of A1, loop through each row. Group the sequence in the row, and the @p option means to take the position number of the member in the original sequence after grouping. Finally, use the rvs function to sort in reverse order

 

A3 Loop through each row in A2, use the position numbers of members in each group to take the corresponding index names from the first row in A1 and concatenate them with commas

 

A4 Use \n to concatenate the sequence rows in A3, and use \t to concatenate the members in the row into a string

 

A5 Put the string in A4 into the clipboard

 

Open the book1.xlsx file first, select the B2:E3 area, press Ctrl+c to copy, and then run the SPL script. After the run is over, return to Excel, click F2 cell, and press Ctrl+v to paste the result.


 
 •  281 View  •