#125 — Grouping Upon Encountering Blank Rows

Problem description & analysis:

Here below is a data table:

Task: The data consist of multiple segments, and each segment includes N consecutive rows and 1 blank row. Now we want to calculate the sum of the values of each segment, and fill in the result in the cell of the blank row, column C, and keep the rest cells in column C null.

Solution:

Use SPL XLL and enter the formula in cell C1:

=spl("=?1.conj().group@i(~[-1]==null).([null]*(~.len()-1)|~.sum()).conj().new(~:Total)",B2:B21)

The results are as follows:

When the previous member is null, create a new group.
Loop through every group, calculate the sum of the values of each group, and make up null values in the cells above the sum cell of each group (the number of nulls should be the number of members minus 1).

---

Download esProc Desktop for FREE and have a brighter 2025 using SPL XLL!! 🚀✨⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming