Distributing funds across multiple lines

[Cr864]

Good afternoon! I was looking for some help here. I have an extremely large list of accounts with fees due (A11:C30). There will be multiple entries for each account number with different amounts due. I have a list of the accounts with the available funds that can be applied. (A3:B7).

What i would like to do is take the funds and for each fee, apply as much to that fee as possible then move to the next one so for instance in the range E11:F30 in my example you can see that the Account 1234 has 550 available, after allocating 50 to the fee in E12, F12 shows the remaining funds we'd have. Then continues through the listings with that account. In my data, all of the accounts will be grouped together but they will potentially have different numbers of fees/line items that need allocation.

In reality, there are about 7000 line items I need to go through with around 900 accounts. I was looking for some help with some VBA in excel that could help accomplish this?

Thank you all for any help you can provide! And if you need more context i'll be happy to provide it.

Test File.xlsx
	A	B	C	D	E	F
1	Example
2
3	Account	Available Funds
4	1234	550
5	5678	250
6	90123	1000
7	456789	10000
8
9
10
11	Fees Due				Expected Results
12	1234	Fee 1	50		50	500
13	1234	Fee 2	250		250	250
14	1234	Fee 3	100		100	150
15	1234	Fee 4	125		100	50
16	5678	Fee 1	25		25	225
17	5678	Fee 2	15		15	210
18	5678	Fee 3	30		30	180
19	5678	Fee 4	200		180	0
20	5678	Fee 5	50		0	0
21	90123	Fee 1	1000		1000	0
22	90123	Fee 2	200		0	0
23	90123	Fee 3	200		0	0
24	90123	Fee 4	300		0	0
25	90123	Fee 5	400		0	0
26	90123	Fee 6	50		0	0
27	456789	Fee 1	5000		5000	5000
28	456790	Fee 2	4000		4000	1000
29	456791	Fee 3	1000		1000	0
30	456792	Fee 4	50		0	0
Sheet4

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C	D	E	F
1	Example
2
3	Account	Available Funds
4	1234	550
5	5678	250
6	90123	1000
7	456789	10000
8
9
10
11	Fees Due
12	1234	Fee 1	50		50	500
13	1234	Fee 2	250		250	250
14	1234	Fee 3	100		100	150
15	1234	Fee 4	125		125	25
16	5678	Fee 1	25		25	225
17	5678	Fee 2	15		15	210
18	5678	Fee 3	30		30	180
19	5678	Fee 4	200		180	0
20	5678	Fee 5	50		0	0
21	90123	Fee 1	1000		1000	0
22	90123	Fee 2	200		0	0
23	90123	Fee 3	200		0	0
24	90123	Fee 4	300		0	0
25	90123	Fee 5	400		0	0
26	90123	Fee 6	50		0	0
27	456789	Fee 1	5000		5000	5000
28	456789	Fee 2	4000		4000	1000
29	456789	Fee 3	1000		1000	0
30	456789	Fee 4	50		0	0
Sheet7

Cell Formulas
Range	Formula
E12:E30	E12	=MAX(0,C12+MIN(0,XLOOKUP(A12,$A$4:$A$7,$B$4:$B$7,0,0)-SUM(FILTER(C$12:C12,A$12:A12=A12))))
F12:F30	F12	=MAX(0,XLOOKUP(A12,$A$4:$A$7,$B$4:$B$7,0,0)-SUM(FILTER(C$12:C12,A$12:A12=A12)))

or

MrExcelPlayground12.xlsx
	A	B	C	D	E	F
1	Example
2
3	Account	Available Funds
4	1234	550
5	5678	250
6	90123	1000
7	456789	10000
8
9
10
11	Fees Due
12	1234	Fee 1	50		50	500
13	1234	Fee 2	250		250	250
14	1234	Fee 3	100		100	150
15	1234	Fee 4	125		125	25
16	5678	Fee 1	25		25	225
17	5678	Fee 2	15		15	210
18	5678	Fee 3	30		30	180
19	5678	Fee 4	200		180	0
20	5678	Fee 5	50		0	0
21	90123	Fee 1	1000		1000	0
22	90123	Fee 2	200		0	0
23	90123	Fee 3	200		0	0
24	90123	Fee 4	300		0	0
25	90123	Fee 5	400		0	0
26	90123	Fee 6	50		0	0
27	456789	Fee 1	5000		5000	5000
28	456789	Fee 2	4000		4000	1000
29	456789	Fee 3	1000		1000	0
30	456789	Fee 4	50		0	0
Sheet7

Cell Formulas
Range	Formula
E12:F30	E12	=LET(a,XLOOKUP(A12,$A$4:$A$7,$B$4:$B$7,0,0)-SUM(FILTER(C$12:C12,A$12:A12=A12)),b,MAX(0,a),c,MAX(0,C12+MIN(0,a)),IF(SEQUENCE(1,2)=1,c,b))
Dynamic array formulas.

 

[Cr864]

@JamesCanale This worked fantastic. Thank you!