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