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.