Problem description & analysis:

We have an annual and monthly water consumption data table for the water meter and part of the data is shown below:

We also have a statistical table for annual water leakage amount:

Task: Now we want to assign the annual water leakage amount to the Water leakage column of the first table according to the proportion of monthly water consumption in the total water consumption of the year (to calculate in cell D1).

Solution:

Use SPL XLL and enter the following code:

 A
1 =E(‘A1:C44’).derive(‘Water leakage’)
2 =E(‘Sheet2!A1:B5’)
3 =A1.group(Year)
4 =A3.run(a=A2.select@1(Year==A3.Year).‘Water leakage’,s=~.sum(Water),~.run(‘Water leakage’=Water*a/s))
5 return A1.new(‘Water leakage’)

A3: Group the data in A1 by Year.
A4: Loop through every group in A3; the variable a is the leakage loss of the corresponding year selected from A2; the variable s is the total Water consumption in this year, and then loop through all rows in this year; assign the Water leakage column as Water*a/s.
A5: Return to A1.

The results are as follows:

Download esProc Desktop for FREE and let simple SPL XLL formulas do all the heavy lifting for you!!! 🚀🔥⬇️

✨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