Problem description & analysis:

Here below is an inventory data table:

r/esProc_Desktop - Early-Terminated Accumulation

Task: We know that the quantity of this product sold today is 50, and want to calculate the new inventory data (subtract the inventory quantity in turn according to the order in the table until 50 are subtracted in total, and keep only the rows with inventory quantity greater than 0).

Solution:

Use SPL XLL and enter the following code:

	A

1	\=E(‘A1:D18’)
2	\=A1.iterate((a=min(Quantity,~~),Quantity-=a,~~-a),50,~~==0)
3	return A1.select(Quantity>0)

A2: Use iterate to loop the iteration, the ~~ in the loop represents the result of the last iteration, and its initial value is set to 50. Take the minimum value of the quantity of the current row and ~~, and assign the value to the variable a; subtract a from the quantity of the current row, and take ~~-a as the result of this iteration; When the iteration result ~~ is 0, terminate the iteration.
A3: Select the rows with quantity>0 in A1 after iteration.

The results are shown below:

r/esProc_Desktop - Early-Terminated Accumulation

Download esProc Desktop for FREE and make data work 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

#83 - Early-Terminated Accumulation

Problem description & analysis:

Solution:

Subscribe to my newsletter

esProc Desktop

esProc Desktop