This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-07-17T03:00:41+00:00) #27 — Group and Summarize A Table Where Every N Rows Consists of A Range by Column. Retrieved from https://www.scien.cx/2024/07/17/27-group-and-summarize-a-table-where-every-n-rows-consists-of-a-range-by-column/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.