Cumulative Totals

This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.