#27 — Group and Summarize A Table Where Every N Rows Consists of A Range by Column

Problem description & analysis:

Below is an Excel table. Every two rows form a range; and in each range, each pair of cells up and down is regarded as a piece of data that stores client and working hours that can be empty.

We need to find the hours of work for each client.

Solution:

Use SPL XLL to do this:

=spl("=E@b(?.group((#-1)\2).conj(E@pb(~)).groups(#1;sum(#2)))",A1:G4)

As shown in the picture below:

Explanation:

group()function groups rows and retains the grouping result details. groups() function performs grouping and aggregation; # represents the current sequence number in a sequence, and ~ is the current member of a sequence. E@pb converts a sequence to a table sequence without column headers.

Comments

[Judith-Excel-Sharing]

If you are interested in the potential of SPL XLL, here's more information for you:
🔅SPL download address: scudata.com/download-Desktop
🌙Plugin Installation Method: c.scudata.com/article/1652061135502
✨References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
💫SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

[Judith-Excel-Sharing]

Feel Free to discuss more Excel tasks with us! Any questions are welcome there:
🍀Discord: discord.gg/hgbKEvJ4
🍀Reddit: reddit.com/r/esProc_Desktop/