Reducing balance and moving to next column

[Jessica553]

I have a distribution formula that I can't figure out. I have created a basic version of what I want the result to be below.
Basically the Row 1 items need to be distributed based on the 'pools' or balances in column a. IE B1 has 100 to be distributed. It takes 10 from A2, 12 from A3, 78 from A4. It's made up to 100. Then it moves to Cell C1 for 20. It picks up 20 from the remainder in Cell A4. Then goes to Cell D1. Picks up the last of Cell A4 and moves to A5 and so on. Tried to do a If then >< formula but it just got really complicated with balances etc.

Thank you

	A	B	C	D	E	F	G
1		100​	20​	18​	21​		159​
2	10​	10​
3	12​	12​
4	115​	78​	20​	17​
5	18​			1​	17​
6	4​				4​
7
8	159​	100​	20​	18​	21​

 

[Peter_SSs]

Could you use something like this?

I have inserted a new row 2 and a new column B. Both are empty and have been hidden.
The formula in C3 is now copied across and down.

23 12 14.xlsm
	A		C	D	E	F	G	H
1			100	20	18	21		159
3	10		10					10
4	12		12					12
5	115		78	20	17			115
6	18				1	17		18
7	4					4		4
8
9	159		100	20	18	21
Distribute

Cell Formulas
Range	Formula
C3:F7	C3	=IF(OR(SUM(C$2:C2)=C$1,SUM($B3:B3)=$A3),"",MIN(C$1-SUM(C$2:C2),$A3-SUM($B3:B3)))
H3:H7	H3	=SUM(C3:F3)
C9:F9	C9	=SUM(C3:C8)

 

[Jessica553]

Could you use something like this?

I have inserted a new row 2 and a new column B. Both are empty and have been hidden.
The formula in C3 is now copied across and down.

Thank you so much Peter, I have added it to my real figures and it works perfectly! Thank you!

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.