How to allocate values in Excel to multiple group based on some criteria?

[mehmoodhamid]

I have first first 4 rows in excel for Product-A costs, 5th is Product-B cost and 6th row is Product-C cost. Now I have all rows below (from 7th to onward) are funds to allocate to above costs. What I want is, add up below costs up to the row to first equal to Product-A costs and allocate to Product-A, by writing in front of each funds as Product-A. If row does not equal exactly then split the row into two rows and allocate first friction to Product-A then other to Product-B. Then continue to Product-B until Product C. At the end show the positive or negative difference.

 

[Robert Mika]

I have first first 4 rows in excel for Product-A costs, 5th is Product-B cost and 6th row is Product-C cost. Now I have all rows below (from 7th to onward) are funds to allocate to above costs. What I want is, add up below costs up to the row to first equal to Product-A costs and allocate to Product-A, by writing in front of each funds as Product-A. If row does not equal exactly then split the row into two rows and allocate first friction to Product-A then other to Product-B. Then continue to Product-B until Product C. At the end show the positive or negative difference.

Hello and welcome to Mr.Excel.
Could you post sample of your data (please see below how to do it) with desired result?

 

[mehmoodhamid]

Hello and welcome to Mr.Excel.
Could you post sample of your data (please see below how to do it) with desired result?

Thanks for your response.

Product-A: 500
Product-B: 350
Product-C 100

Funds to allocate
150
35
50
95
200
100
275
40

We need to add funds and allocate exact amount against Product-A. Where it exceeds Product-A cost then it should be split and the difference should be allocated against Product-B. Same should be allocated against Product-C.

We have no probelm allocating from 150 to 95 funds against Product-A. But when it comes to fund 200, we have excess of 30. This 30 should be allocated against product-B. Untill we come to 275, again we have excess of 55 which should be allocated to product C and then 40.

At the end we have difference, in + or -. Simply display this whcih is -5 in above case.