Multiple invoices and Multiple payments formula

[testinglife]

Hello I asked something similar to this already, but I figured I would ask again to make it more clear what I am looking for.

See picture below of my scenario. I have multiple invoices. I also have received multiple payments (but they were not specified for specific invoices). What I am looking for is a way using formula to apply payments to these invoices. However my main objective is to fill out the columns called "Invoice applied to" and "Payment Name". Both would be great but honestly as long as one or the other is filled in then it achieves my goal. Basically with those columns, if a payment was applied to an invoice, I would just want the payment name listed beside that invoice, or the invoice name listed beside that payment. That might require multiple invoices to be listed beside the payment or it might require multiple payments to be listed beside the invoice.

The formula will need to check if the invoice still has room for more payments (so there isn't an overpayment applied to one invoice), but will also need to potentially apply more than 1 payment to a single invoice, if it's not fully paid yet.




I don't mind using VBA either if you think that is the best solution.

I have found many many many solutions that help me make an excel that takes the total payments and applies that to the invoices, and that is not what I am looking for because that does not show me what "payment names" were applied to what invoices. It just basically uses a sumif and a min/max formula to say there was payment still available and so it "x" amount was applied to that invoice.

Thanks for the help !

 

[Eric W]

Let's say your sheet is set up like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100	100	100 from payment P1		1	P1	100	100	100 applied to A
3	1	B	100	100	75 from payment P2, 25 from payment P3		1	P2	75	75	75 applied to B
4	1	C	100	100	100 from payment P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E
5	2	D	200	200	140 from payment P21, 60 from payment P22		1	P4	35	35	35 applied to E
6	1	E	100	100	25 from payment P3, 35 from payment P4, 22 from payment P5, 18 from payment P6		1	P5	22	22	22 applied to E
7	2	F	300	15	15 from payment P22		1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J
8	3	G	50				1	P7	80	43	43 applied to J
9	2	H	75				2	P21	140	140	140 applied to D
10	1	I	100	100	100 from payment P6		2	P22	75	75	60 applied to D, 15 applied to F
11	1	J	125	125	82 from payment P6, 43 from payment P7
12
Sheet20

Invoices in columns A:E. Payments in columns G:K. Then you can set up a Change event to update the D:E and K:L columns. Open a new workbook to experiment on. Put some data in A1:C10. Then right click on the sheet tab and select View Code. Paste the following in the sheet that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As Variant, Cust As String, PayName As String, amt As Double, i As Long, owing As Double
Dim PayMsg As String

'Exit Sub
    If Target.Column <> 9 Then Exit Sub
    
    Application.EnableEvents = False
    
    MyData = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Cust = Target.Offset(, -2).Value
    PayName = Target.Offset(, -1).Value
    amt = Target.Value
    
    PayMsg = ""
    For i = 1 To UBound(MyData)
        owing = MyData(i, 3) - MyData(i, 4)
        If owing > 0 And MyData(i, 1) = Cust Then
            owing = WorksheetFunction.Min(owing, amt)
            MyData(i, 4) = MyData(i, 4) + owing
            mycomma = IIf(MyData(i, 5) = "", "", ", ")
            MyData(i, 5) = MyData(i, 5) & mycomma & owing & " from " & PayName
            PayMsg = PayMsg & ", " & owing & " applied to " & MyData(i, 2)
            amt = amt - owing
        End If
        If amt = 0 Then Exit For
    Next i
    
    PayMsg = Mid(PayMsg, 3)
    Target.Offset(, 1) = Target - amt
    Target.Offset(, 2).Value = PayMsg
    Range("A2").Resize(UBound(MyData), 5).Value = MyData
    
    Application.EnableEvents = True
    
    
End Sub

Then start entering data in columns G, H, I. When you enter the amount in column I, the macro will kick off and figure out where to apply it. What you see in the sample above is the results of my testing. Note that Payment P7 still has 37 dollars unapplied. Invoice F is partially covered, and no payments have been made to customer 3. I'm considering writing another macro that checks when you add an invoice in A:C, it checks to see if there's any outstanding total left in column K it needs to apply. Like if Customer 1 added an invoice for 37 dollars, it would check the G:L columns, see that Customer 1 has a balance of 37 and apply it right away. But try this first and see what you think.

I actually think you could do this with formulas, but they'd be pretty complicated. Does your version of Excel have TEXTJOIN, or the new dynamic array functions like FILTER?

 

[testinglife]

Let's say your sheet is set up like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100	100	100 from payment P1		1	P1	100	100	100 applied to A
3	1	B	100	100	75 from payment P2, 25 from payment P3		1	P2	75	75	75 applied to B
4	1	C	100	100	100 from payment P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E
5	2	D	200	200	140 from payment P21, 60 from payment P22		1	P4	35	35	35 applied to E
6	1	E	100	100	25 from payment P3, 35 from payment P4, 22 from payment P5, 18 from payment P6		1	P5	22	22	22 applied to E
7	2	F	300	15	15 from payment P22		1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J
8	3	G	50				1	P7	80	43	43 applied to J
9	2	H	75				2	P21	140	140	140 applied to D
10	1	I	100	100	100 from payment P6		2	P22	75	75	60 applied to D, 15 applied to F
11	1	J	125	125	82 from payment P6, 43 from payment P7
12
Sheet20

Invoices in columns A:E. Payments in columns G:K. Then you can set up a Change event to update the D:E and K:L columns. Open a new workbook to experiment on. Put some data in A1:C10. Then right click on the sheet tab and select View Code. Paste the following in the sheet that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As Variant, Cust As String, PayName As String, amt As Double, i As Long, owing As Double
Dim PayMsg As String

'Exit Sub
    If Target.Column <> 9 Then Exit Sub
  
    Application.EnableEvents = False
  
    MyData = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Cust = Target.Offset(, -2).Value
    PayName = Target.Offset(, -1).Value
    amt = Target.Value
  
    PayMsg = ""
    For i = 1 To UBound(MyData)
        owing = MyData(i, 3) - MyData(i, 4)
        If owing > 0 And MyData(i, 1) = Cust Then
            owing = WorksheetFunction.Min(owing, amt)
            MyData(i, 4) = MyData(i, 4) + owing
            mycomma = IIf(MyData(i, 5) = "", "", ", ")
            MyData(i, 5) = MyData(i, 5) & mycomma & owing & " from " & PayName
            PayMsg = PayMsg & ", " & owing & " applied to " & MyData(i, 2)
            amt = amt - owing
        End If
        If amt = 0 Then Exit For
    Next i
  
    PayMsg = Mid(PayMsg, 3)
    Target.Offset(, 1) = Target - amt
    Target.Offset(, 2).Value = PayMsg
    Range("A2").Resize(UBound(MyData), 5).Value = MyData
  
    Application.EnableEvents = True
  
  
End Sub

Then start entering data in columns G, H, I. When you enter the amount in column I, the macro will kick off and figure out where to apply it. What you see in the sample above is the results of my testing. Note that Payment P7 still has 37 dollars unapplied. Invoice F is partially covered, and no payments have been made to customer 3. I'm considering writing another macro that checks when you add an invoice in A:C, it checks to see if there's any outstanding total left in column K it needs to apply. Like if Customer 1 added an invoice for 37 dollars, it would check the G:L columns, see that Customer 1 has a balance of 37 and apply it right away. But try this first and see what you think.

I actually think you could do this with formulas, but they'd be pretty complicated. Does your version of Excel have TEXTJOIN, or the new dynamic array functions like FILTER?

Sorry on the slow reply, this is great ! I was having trouble getting it to work at first but somehow my macro's were disabled. I got then enabled and this works very well.

In a normal situation I would copy in all the payments into this sheet all at once. I can easily copy them into the format you presented. I guess the only thing is if I copy in a group of data I have to click on each column "i" and hit enter for the macro to run. Is there a way to make it auto run without need to hit enter on each column i cell? Such as once I copy something in I hit run and then it run the macro for all of column i, top to bottom?

Another thing I notice (and I am just thinking of common errors I will create by my human error), if I re hit enter on a column i cell it will re apply that payment line. I was thinking of adding in an extra row that says Fully applied? (see below) that just checks to see if the payment is fully used. Is there anything we can add to the macro that will make it do a check for column i to see if column L is 0 and if it is to do nothing?

test formula.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)	Fully Applied?
2	1	A	100	100	100 from P1		1	P1	100	100	100 applied to A	0
3	1	B	100	100	75 from P2, 25 from P3		1	P2	75	75	75 applied to B	0
4	1	C	100	100	100 from P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E	0
5	2	D	200	200	140 from P21, 60 from P22		1	P4	35	35	35 applied to E	0
6	1	E	100	100	25 from P3, 35 from P4, 22 from P5, 18 from P6		1	P5	22	22	22 applied to E	0
7		F	300				1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J	0
8	3	G	50				1	P7	80	43	43 applied to J	37
9		H	75				2	P21	140	140	140 applied to D	0
10	1	I	100	100	100 from P6		2	P22	75	60	60 applied to D	15
Sheet1

Cell Formulas
Range	Formula
L2:L10	L2	=I2-J2

 

[testinglife]

Does your version of Excel have TEXTJOIN, or the new dynamic array functions like FILTER?

Sorry I forgot to answer this. I believe it does have this. I have the newest version of excel, but I just tried typing =filter and =textjoin and brought up the option to fill it out.

 

[Eric W]

OK, try this. Remove the previous macro from your workbook, or start with a fresh workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. Paste this code into the sheet that opens:

Sub ApplyPayments()
Dim MyInvoices As Variant, MyPayments As Variant
Dim Cust As String, PayName As String, PayMsg As String
Dim amt As Double, owing As Double, i As Long, j As Long
    
    MyInvoices = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    MyPayments = Range("G2:K" & Cells(Rows.Count, "G").End(xlUp).Row).Value
    
    For i = 1 To UBound(MyPayments)
        If MyPayments(i, 4) = MyPayments(i, 3) Then GoTo NextInv:
        
        Cust = MyPayments(i, 1)
        PayName = MyPayments(i, 2)
        amt = MyPayments(i, 3)
    
        PayMsg = ""
        For j = 1 To UBound(MyInvoices)
            owing = MyInvoices(j, 3) - MyInvoices(j, 4)
            If owing > 0 And MyInvoices(j, 1) = Cust Then
                owing = WorksheetFunction.Min(owing, amt)
                MyInvoices(j, 4) = MyInvoices(j, 4) + owing
                mycomma = IIf(MyInvoices(j, 5) = "", "", ", ")
                MyInvoices(j, 5) = MyInvoices(j, 5) & mycomma & owing & " from " & PayName
                PayMsg = PayMsg & ", " & owing & " applied to " & MyInvoices(j, 2)
                amt = amt - owing
            End If
            If amt = 0 Then Exit For
        Next j
        PayMsg = Mid(PayMsg, 3)
        MyPayments(i, 4) = MyPayments(i, 3) - amt
        MyPayments(i, 5) = PayMsg
NextInv:
    Next i
    
    Range("A2").Resize(UBound(MyInvoices), 5).Value = MyInvoices
    Range("G2").Resize(UBound(MyPayments), 5).Value = MyPayments
     
End Sub

Press Alt-Q to close the editor.

Now starting with this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100				1	P1	100
3	1	B	100				1	P2	75
4	1	C	100				1	P3	150
5	2	D	200				1	P4	35
6	1	E	100				1	P5	22
7	2	F	300				1	P6	200
8	3	G	50
9	2	H	75
10	1	I	100
11	1	J	125
Sheet20

Press Alt-F8 to open the macro selector, select ApplyPayments and click Run. You should get this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100	100	100 from P1		1	P1	100	100	100 applied to A
3	1	B	100	100	75 from P2, 25 from P3		1	P2	75	75	75 applied to B
4	1	C	100	100	100 from P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E
5	2	D	200				1	P4	35	35	35 applied to E
6	1	E	100	100	25 from P3, 35 from P4, 22 from P5, 18 from P6		1	P5	22	22	22 applied to E
7	2	F	300				1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J
8	3	G	50
9	2	H	75
10	1	I	100	100	100 from P6
11	1	J	125	82	82 from P6
Sheet20

I now added 3 more payments:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100	100	100 from P1		1	P1	100	100	100 applied to A
3	1	B	100	100	75 from P2, 25 from P3		1	P2	75	75	75 applied to B
4	1	C	100	100	100 from P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E
5	2	D	200				1	P4	35	35	35 applied to E
6	1	E	100	100	25 from P3, 35 from P4, 22 from P5, 18 from P6		1	P5	22	22	22 applied to E
7	2	F	300				1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J
8	3	G	50				1	P7	80
9	2	H	75				2	P21	140
10	1	I	100	100	100 from P6		2	P22	75
11	1	J	125	82	82 from P6
Sheet20

Ran it again and got this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Invoice Name	Invoice Amount	Applied Amount	Invoice Name(s)		Customer	Payment Name	Payment Amount	Applied Amount	Invoice(s)
2	1	A	100	100	100 from P1		1	P1	100	100	100 applied to A
3	1	B	100	100	75 from P2, 25 from P3		1	P2	75	75	75 applied to B
4	1	C	100	100	100 from P3		1	P3	150	150	25 applied to B, 100 applied to C, 25 applied to E
5	2	D	200	200	140 from P21, 60 from P22		1	P4	35	35	35 applied to E
6	1	E	100	100	25 from P3, 35 from P4, 22 from P5, 18 from P6		1	P5	22	22	22 applied to E
7	2	F	300	15	15 from P22		1	P6	200	200	18 applied to E, 100 applied to I, 82 applied to J
8	3	G	50				1	P7	80	43	43 applied to J
9	2	H	75				2	P21	140	140	140 applied to D
10	1	I	100	100	100 from P6		2	P22	75	75	60 applied to D, 15 applied to F
11	1	J	125	125	82 from P6, 43 from P7
Sheet20

No need to add another column, I just check to see if the Applied amount matches the Payment amount. You can add a shortcut to the macro from the macro selector - press the Options button. Or even add a button to the ribbon or Quick Access menu.

Let me know if this works for you.