Formula to apply payments from one column to balances in another

[testinglife]

Hello,

See attached picture for an example of what the data might look like. Basically this is my question I have a bunch of invoices on the left side of the excel and a bunch of payments on the right side of the excel. There is an ID to identify a customer so we know what invoices are their and what payments apply to that customers invoices. I am wondering if there is a formula that anyone can recommend that will take a payment from the right and apply it to an invoice on the left, using the ID to match them up. It should only apply a payment if there is a remaining amount on one of the invoices, it should also keep applying to the same invoice until it is fully paid and then move onto the next invoice and start paying that. And lastly it should only apply a payment if there is an invoice available (sometimes they may pay before we issue an invoice). Unfortunately I need it applied invoice by invoice and not on a total basis or I would have just done a sumifs for that customers outstanding balance.

I am curious to see what everyone else recommends. I have come up with some formulas but they are messy and I just know there is better way to do this.

 

[MDCurry]

Hi,
I have a solution that could possibly work for you...but it is messy too.
Based on your attachment:
1. In cell D3 enter =MAX(-C3,SUMIFS(K:K,I:I,A3)) ...returns the amount paid on the 1st Invoice record not to exceed the invoice
2. In Cell E3 enter =SUM(C3:D3) ...shows the Invoice Balance outstanding
3. In Cell D4 enter =IF(AND(A4=A3,E3>0),0,MAX(-C4,SUMIFS(K:K,I:I,A4)+SUMIFS(C$3:C3,A$3:A3,A4,E$3:E3,0))) ...returns amount paid on remaining invoices
4. Copy D4 and E3 formulas down

 

[testinglife]

Hi,
I have a solution that could possibly work for you...but it is messy too.
Based on your attachment:
1. In cell D3 enter =MAX(-C3,SUMIFS(K:K,I:I,A3)) ...returns the amount paid on the 1st Invoice record not to exceed the invoice
2. In Cell E3 enter =SUM(C3:D3) ...shows the Invoice Balance outstanding
3. In Cell D4 enter =IF(AND(A4=A3,E3>0),0,MAX(-C4,SUMIFS(K:K,I:I,A4)+SUMIFS(C$3:C3,A$3:A3,A4,E$3:E3,0))) ...returns amount paid on remaining invoices
4. Copy D4 and E3 formulas down

I appreciate the help, this is similar to what I already have. However I need to know what payment is being applied to what invoice. Sorry I wasn't really clear when I said that in my original post, but that's what I was meaning when I said I can't do thing on a total basis. So how you recommend is invoice by invoice which is great, but it sums all available payments and applies it on a total basis. Is there anything we can add onto this that would make it where in column F or something we could say what payments make it up? I am just realizing now that I didn't include the invoice ID's or the payment ID's but you can assume that every payment has a unique ID. So it will be like PY000225 or something like that. And likewise for each invoice. So in the case of the first of the first invoice for customer ID 00039 we say that there were 4 payments that made up what we applied to the first invoice. So in column f it might show something like PY00001,PY00002,PY00003,PY00004 (where these payment ID's pulled the actual payment ID's which I didn't post).

Not sure if my explanation was clear, or if what I am asking is even possible.

 

[MDCurry]

I am not aware of any formula to achieve what you describe.
Sorry