Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)

[staticfluids]

Hi all, I hope you're doing well.
First of all, sorry for bad explanation as my main language is not English.
I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula. It's quite helping with my daily Excel use. 2 of my latest question was asking about Dynamic Array since I'm very new into this.

I wanted to ask. Refer to this post, I may have the same logic as the OP.
I wanted to distribute value from F:F to fill the value from B:B. I got the logic but turning it into formula is a bit tricky for me that's novice to this area. And I'm not sure if this is increment or not because I'm using a translator app. I think this is only a number distribution.

Anyway, the logic is:
SUMIF the same date on D:D for F:F cell -->
LOOK into the oldest date on D:D for F:F cell -->
take value at 1 cell at F -->
LOOK into the oldest date on A:A for B:B cell -->
SUBTRACT the value at 1 cell at F to the 1 cell at B -->
IF the current F cell has 0 value but the current B cell still have value, continue to the next F cell and DISTRIBUTE the value at F to B until the B value is 0 -->
IF the B value is 0 but F cell still have value, continue to the next B cell and DISTRIBUTE the value at F to B until the F value is 0 --> and so on until the F value on the last cell is 0 or having remaining value.

IF the value at F has decimals or remaining value, leave it at the end of the cell (or in the example, leave it at the top cell).

I think, the formula should be fill only at J:J and K:K. Or maybe from H:H until K:K but having a different formula for each first cell.

Example expected array:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Month Period	Value		Date	Type	Value		Month Period	Value from B:B	Date	Value from F:F
2	01 September 2022	£ 100.00		04 November 2022	Transfer	£ 400.00		01 September 2022	£ 100.00	04 November 2022	£100.00
3	01 August 2022	£ 100.00		12 July 2022	Transfer	£ 25.00		01 August 2022	£ 100.00	04 November 2022	£82.50
4	01 July 2022	£ 100.00		02 July 2022	Transfer	£ 100.00				12 July 2022	£17.50
5	01 June 2022	£ 100.00		06 June 2022	Transfer	£ 25.00		01 July 2022	£ 100.00	12 July 2022	£7.50
6	01 May 2022	£ 100.00		26 May 2022	Transfer	£ 75.00				02 July 2022	£92.50
7	01 April 2022	£ 100.00		05 May 2022	Transfer	£ 2.50		01 June 2022	£ 100.00	02 July 2022	£7.50
8	01 March 2022	£ 100.00		05 May 2022	Transfer	£ 50.00				06 June 2022	£25.00
9	01 February 2022	£ 200.00		22 April 2022	Transfer	£ 110.00				26 May 2022	£67.50
10				01 April 2022	Transfer	£ 100.00		01 May 2022	£ 100.00	26 May 2022	£7.50
11				13 March 2022	Transfer	£ 100.00				05 May 2022	£52.50
12				28 February 2022	Transfer	£ 100.00				22 April 2022	£40.00
13				11 February 2022	Transfer	£ 3.00		01 April 2022	£ 100.00	22 April 2022	£70.00
14				11 February 2022	Transfer	£ 6.00				01 April 2022	£30.00
15				11 February 2022	Transfer	£ 1.00		01 March 2022	£ 100.00	01 April 2022	£70.00
16				26 January 2022	Transfer	£ 20.00				13 March 2022	£30.00
17								01 February 2022	£ 200.00	13 March 2022	£70.00
18										28 February 2022	£100.00
19										11 February 2022	£10.00
20										26 January 2022	£20.00
Sheet2

Example if the one last cell on F is having more value than the last cell on B:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Month Period	Value		Date	Type	Value		Month Period	Value from B:B	Date	Value from F:F
2	01 September 2022	£ 100.00		04 November 2022	Transfer	£ 400.00		01 September 2022	£ 100.00	04 November 2022	£217.50
3	01 August 2022	£ 100.00		12 July 2022	Transfer	£ 25.00		01 August 2022	£ 100.00	04 November 2022	£82.50
4	01 July 2022	£ 100.00		02 July 2022	Transfer	£ 100.00				12 July 2022	£17.50
5	01 June 2022	£ 100.00		06 June 2022	Transfer	£ 25.00		01 July 2022	£ 100.00	12 July 2022	£7.50
6	01 May 2022	£ 100.00		26 May 2022	Transfer	£ 75.00				02 July 2022	£92.50
7	01 April 2022	£ 100.00		05 May 2022	Transfer	£ 2.50		01 June 2022	£ 100.00	02 July 2022	£7.50
8	01 March 2022	£ 100.00		05 May 2022	Transfer	£ 50.00				06 June 2022	£25.00
9	01 February 2022	£ 200.00		22 April 2022	Transfer	£ 110.00				26 May 2022	£67.50
10				01 April 2022	Transfer	£ 100.00		01 May 2022	£ 100.00	26 May 2022	£7.50
11				13 March 2022	Transfer	£ 100.00				05 May 2022	£52.50
12				28 February 2022	Transfer	£ 100.00				22 April 2022	£40.00
13				11 February 2022	Transfer	£ 3.00		01 April 2022	£ 100.00	22 April 2022	£70.00
14				11 February 2022	Transfer	£ 6.00				01 April 2022	£30.00
15				11 February 2022	Transfer	£ 1.00		01 March 2022	£ 100.00	01 April 2022	£70.00
16				26 January 2022	Transfer	£ 20.00				13 March 2022	£30.00
17								01 February 2022	£ 200.00	13 March 2022	£70.00
18										28 February 2022	£100.00
19										11 February 2022	£10.00
20										26 January 2022	£20.00
Sheet3

I attach the full expected array below, please have a look:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Month Period	Value		Date	Type	Value		Month Period	Value from B:B	Date	Value from F:F
2	01 April 2024	£ 200.00		18 April 2024	Transfer	£ 51.75		01 April 2024	£ 200.00	18 April 2024	£51.75
3	01 March 2024	£ 200.00		17 April 2024	Transfer	£ 465.75				17 April 2024	£148.25
4	01 February 2024	£ 100.00		21 February 2024	Transfer	£ 465.00		01 March 2024	£ 200.00	17 April 2024	£200.00
5	01 January 2024	£ 100.00		12 February 2024	Transfer	£ 400.00		01 February 2024	£ 100.00	17 April 2024	£100.00
6	01 December 2023	£ 200.00		24 January 2024	Transfer	£ 200.00		01 January 2024	£ 100.00	17 April 2024	£17.50
7	01 November 2023	£ 200.00		26 September 2023	Transfer	£ 200.00				21 February 2024	£82.50
8	01 October 2023	£ 100.00		28 May 2023	Transfer	£ 300.00		01 December 2023	£ 200.00	21 February 2024	£200.00
9	01 September 2023	£ 100.00		31 March 2023	Transfer	£ 100.00		01 November 2023	£ 200.00	21 February 2024	£182.50
10	01 August 2023	£ 200.00		30 January 2023	Transfer	£ 100.00				12 February 2024	£17.50
11	01 July 2023	£ 200.00		10 December 2022	Transfer	£ 100.00		01 October 2023	£ 100.00	12 February 2024	£100.00
12	01 June 2023	£ 200.00		04 November 2022	Transfer	£ 400.00		01 September 2023	£ 100.00	12 February 2024	£100.00
13	01 May 2023	£ 100.00		12 July 2022	Transfer	£ 25.00		01 August 2023	£ 200.00	12 February 2024	£182.50
14	01 April 2023	£ 100.00		02 July 2022	Transfer	£ 100.00				24 January 2024	£17.50
15	01 March 2023	£ 100.00		06 June 2022	Transfer	£ 25.00		01 July 2023	£ 200.00	24 January 2024	£182.50
16	01 February 2023	£ 100.00		26 May 2022	Transfer	£ 75.00				26 September 2023	£17.50
17	01 January 2023	£ 100.00		05 May 2022	Transfer	£ 2.50		01 June 2023	£ 200.00	26 September 2023	£182.50
18	01 December 2022	£ 100.00		05 May 2022	Transfer	£ 50.00				28 May 2023	£17.50
19	01 November 2022	£ 100.00		22 April 2022	Transfer	£ 110.00		01 May 2023	£ 100.00	28 May 2023	£100.00
20	01 October 2022	£ 100.00		01 April 2022	Transfer	£ 100.00		01 April 2023	£ 100.00	28 May 2023	£100.00
21	01 September 2022	£ 100.00		13 March 2022	Transfer	£ 100.00		01 March 2023	£ 100.00	28 May 2023	£82.50
22	01 August 2022	£ 100.00		28 February 2022	Transfer	£ 100.00				31 March 2023	£17.50
23	01 July 2022	£ 100.00		11 February 2022	Transfer	£ 3.00		01 February 2023	£ 100.00	31 March 2023	£82.50
24	01 June 2022	£ 100.00		11 February 2022	Transfer	£ 6.00				30 January 2023	£17.50
25	01 May 2022	£ 100.00		11 February 2022	Transfer	£ 1.00		01 January 2023	£ 100.00	30 January 2023	£82.50
26	01 April 2022	£ 100.00		26 January 2022	Transfer	£ 20.00				10 December 2022	£17.50
27	01 March 2022	£ 100.00						01 December 2022	£ 100.00	10 December 2022	£82.50
28	01 February 2022	£ 200.00								04 November 2022	£17.50
29	01 October 2023	£ 100.00						01 November 2022	£ 100.00	04 November 2022	£100.00
30	01 September 2023	£ 100.00						01 October 2022	£ 100.00	04 November 2022	£100.00
31	01 August 2023	£ 200.00						01 September 2022	£ 100.00	04 November 2022	£100.00
32	01 July 2023	£ 200.00						01 August 2022	£ 100.00	04 November 2022	£82.50
33	01 June 2023	£ 200.00								12 July 2022	£17.50
34	01 May 2023	£ 100.00						01 July 2022	£ 100.00	12 July 2022	£7.50
35	01 April 2023	£ 100.00								02 July 2022	£92.50
36	01 March 2023	£ 100.00						01 June 2022	£ 100.00	02 July 2022	£7.50
37	01 February 2023	£ 100.00								06 June 2022	£25.00
38	01 January 2023	£ 100.00								26 May 2022	£67.50
39	01 December 2022	£ 100.00						01 May 2022	£ 100.00	26 May 2022	£7.50
40	01 November 2022	£ 100.00								05 May 2022	£52.50
41	01 October 2022	£ 100.00								22 April 2022	£40.00
42	01 September 2022	£ 100.00						01 April 2022	£ 100.00	22 April 2022	£70.00
43	01 August 2022	£ 100.00								01 April 2022	£30.00
44	01 July 2022	£ 100.00						01 March 2022	£ 100.00	01 April 2022	£70.00
45	01 June 2022	£ 100.00								13 March 2022	£30.00
46	01 May 2022	£ 100.00						01 February 2022	£ 200.00	13 March 2022	£70.00
47	01 April 2022	£ 100.00								28 February 2022	£100.00
48	01 March 2022	£ 100.00								11 February 2022	£10.00
49	01 February 2022	£ 200.00								26 January 2022	£20.00
50								01 October 2023	£ 100.00	12 February 2024	£100.00
51								01 September 2023	£ 100.00	12 February 2024	£100.00
52								01 August 2023	£ 200.00	12 February 2024	£182.50
53										24 January 2024	£17.50
54								01 July 2023	£ 200.00	24 January 2024	£182.50
55										26 September 2023	£17.50
56								01 June 2023	£ 200.00	26 September 2023	£182.50
57										28 May 2023	£17.50
58								01 May 2023	£ 100.00	28 May 2023	£100.00
59								01 April 2023	£ 100.00	28 May 2023	£100.00
60								01 March 2023	£ 100.00	28 May 2023	£82.50
61										31 March 2023	£17.50
62								01 February 2023	£ 100.00	31 March 2023	£82.50
63										30 January 2023	£17.50
64								01 January 2023	£ 100.00	30 January 2023	£82.50
65										10 December 2022	£17.50
66								01 December 2022	£ 100.00	10 December 2022	£82.50
67										04 November 2022	£17.50
68								01 November 2022	£ 100.00	04 November 2022	£100.00
69								01 October 2022	£ 100.00	04 November 2022	£100.00
70								01 September 2022	£ 100.00	04 November 2022	£100.00
71								01 August 2022	£ 100.00	04 November 2022	£82.50
72										12 July 2022	£17.50
73								01 July 2022	£ 100.00	12 July 2022	£7.50
74										02 July 2022	£92.50
75								01 June 2022	£ 100.00	02 July 2022	£7.50
76										06 June 2022	£25.00
77										26 May 2022	£67.50
78								01 May 2022	£ 100.00	26 May 2022	£7.50
79										05 May 2022	£52.50
80										22 April 2022	£40.00
81								01 April 2022	£ 100.00	22 April 2022	£70.00
82										01 April 2022	£30.00
83								01 March 2022	£ 100.00	01 April 2022	£70.00
84										13 March 2022	£30.00
85								01 February 2022	£ 200.00	13 March 2022	£70.00
86										28 February 2022	£100.00
87										11 February 2022	£10.00
88										26 January 2022	£20.00
Sheet1

This is a dynamic data; the data is always updated. So I'm looking a way to find a dynamic array formula to avoid manual editing when the data is updated either on A:B or D:F.

Any help would be much appreciated.
Thank you!

 

[Cubist]

I read this 3 times and couldn't figure out how you got the numbers. You might have to a smaller example.

 

[staticfluids]

Sorry if i'm not good explaining into words. Perhaps this could help understanding the logic.

A:B is the budget per month (forecast)
D:F is the actual money transferred. Notice that the transferred is sometimes not the same as the budget month. Sometimes earlier and sometimes delayed. Sometimes big number sometimes small number. I want to distribute the actual into the forecast.

Book3
	H	I	J	K	L	M	N	O
1	Month Period	Value from B:B	Date	Value from F:F
2	01 September 2022	£ 100.00	04 November 2022	£ 100.00	<--	Last cell
3	01 August 2022	£ 100.00	04 November 2022	£ 82.50	<--	£ 100.00	<----	SUM of J3:J4
4			12 July 2022	£ 17.50
5	01 July 2022	£ 100.00	12 July 2022	£ 7.50	<--	£ 100.00
6			02 July 2022	£ 92.50
7	01 June 2022	£ 100.00	02 July 2022	£ 7.50	<--	£ 100.00
8			06 June 2022	£ 25.00
9			26 May 2022	£ 67.50
10	01 May 2022	£ 100.00	26 May 2022	£ 7.50	<--	£ 100.00
11			05 May 2022	£ 52.50
12			22 April 2022	£ 40.00
13	01 April 2022	£ 100.00	22 April 2022	£ 70.00	<--	£ 100.00
14			01 April 2022	£ 30.00
15	01 March 2022	£ 100.00	01 April 2022	£ 70.00	<--	£ 100.00
16			13 March 2022	£ 30.00
17	01 February 2022	£ 200.00	13 March 2022	£ 70.00	<--	£ 200.00	<----	SUM of J17:J20
18			28 February 2022	£ 100.00
19			11 February 2022	£ 10.00
20			26 January 2022	£ 20.00
Sheet1

Cell Formulas
Range	Formula
M3,M15,M13,M5	M3	=SUM(K3:K4)
M7,M10	M7	=SUM(K7:K9)
M17	M17	=SUM(K17:K20)