...

Add Count Result to Every Row in a Group

 
excel(99) grouping(8)

Problem description

Below is Excel filebook1.xlsx, where the Employee field contains names, the Shift field contains commuting records, and RouteID has shuttle bus IDs.

..

The task is to count the number of employees who take the same bus in one day. The desired result is shown in the green area below:

..

The task involves post-grouping aggregation, intra-group calculation, and the use of the clipboard.

Directions

1. Write the following script:

A

1

=clipboard().import@t().derive(Employees)

2

=A1.group('Route ID').run(a=~.count(),~.run(Employees=a))

3

=A1.group(Employee,Shift)

4

=A3.new(Employee,Shift,"{"/~.(Employees).concat(";")/"}":Clubbing)

5

=clipboard(A4.export@t())

A1Import data from the clipboard and add a new column Employees. @t option enables reading the first row as column headers.

A2 Group A1’s data by RouteID, loop through each group to count the number of records (which is the number of employees who take the same bus), assign the number to variable a, and then loop through each record in the current group to assign a to Employees field.

A3 Group A1 by Employee and Shift.

A4 Create a new record from each group of A3, get the Employee field and Shift field, use a semicolon to join Employees values in this group into a string, enclose it with braces, and rename it Clubbing.

A5 Convert A4’s table sequence into a string to put it onto the clipboard. @t option enables exporting the column headers at conversion.

2. Select area A1:D18 in the Excel file and press Ctrl+C to copy it to the clipboard.

Back to esProc to run the program, and then go back to the Excel file, click G1, and press Ctrl+V to paste the result in.

Q & A Collection  

https://stackoverflow.com/questions/63800133/multi-criteria-search-and-returning-multiple-values-from-a-table-using-array-fun

【Attachment】clubbing.zip


excel(99) grouping(8)
 
 •  183 View  •