...

Grouping by Empty or Non-Empty Rows

 

1. Empty row

There is an Excel file book1.xlsx, and the data is as follows:

..

The data in column A consists of multiple segments, and each segment is composed of continuous N rows + 1 empty row. The task is to calculate the sum of each segment at the empty rows in column B, and other rows in column B remain empty. The results are as follows:

..

Write SPL script:

A

1

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

2

=A1.group@i(~[-1]=="")

3

=A2.([""]*(~.len()-1)|~.sum()).conj()

4

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

A1 Read the data from the clipboard and use the carriage return character to split it into a sequence

A2 When the previous member is an empty string, create a new group

A3 Loop through each group, calculate the sum of the numbers in each group and fill in (number of members-1) empty stings in front

A4 Concatenate the sequence in A3 to a string with \n and put it into the clipboard

Open book1.xlsx, select all valid data in column A, copy with Ctrl+c, and then run the SPL script. After running, return to Excel, click cell B1, and paste the result with Ctrl+v.

2. Non-empty row

The Excel file book1.xlsx has monthly water consumption data for each year. Only the “Year” before the first “Month” of each year is filled in. Part of the data is as follows:

..

The task is to calculate the total water consumption each year, and some of the results are as follows:

..

Write SPL script:

A

1

=T("e:/water/book1.xlsx")

2

=A1.group@i(Year!=null).new(Year,~.sum(Water):Total_Water)

3

=T("e:/water/book2.xlsx",A2)

A1 Read the data of book1.xlsx

A2 Divide the records into a new group when the Year field is not empty. The @i option means generating a new group when the condition is met. Then create a new data set, take out the Year field, and the total water consumption in each year is the Total_Water field

A3 Store the results in A2 to book2.xlsx


 
 •  304 View  •