Add a Summary at the First Row of Each Group
Example
The data of an Excel file Book1.xlsx is as follows:
B |
C |
D |
E |
result |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
Column B is ordered. The task is to group the data by column B, and fill the first “result” of each group with the count value of the group. The results are as follows:
B |
C |
D |
E |
result |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
5 |
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
3 |
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
2 |
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
Write SPL script:
A |
|
1 |
=file("Book1.xlsx").xlsimport@t() |
2 |
=A1.group@o(B).run(~(1).result=~.count()) |
3 |
=file("result.xlsx").xlsexport@t(A1) |
A1 Read the Excel data
A2 Group the data by column B, and assign the count value of each group to the first “result” in the group
A3 Export the result A1 to result.xlsx
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
