Microsoft Excel Tutorial: Which Invoices Add Up To This Check Amount - Faster method using Python in Excel.
To download the workbook from today: Excel Which Invoices Add Up To This Check Amount - 2658 Sample Files - MrExcel Publishing
Today, I'm excited to share a powerful solution to a problem I used to call the "knapsack problem" in accounts receivable. We often face scenarios where customers send a payment without specifying which invoices are covered—leaving us to solve the puzzle! In this video, I'll show you how to identify which invoices match a specific payment amount using Python in Excel, without the need for a Copilot subscription.![Rocket :rocket: 🚀](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f680.png)
This challenge involves finding a combination of invoices that add up to the exact payment amount. For example, when we receive a check for **$54,967.28** from a customer, we need to match it against dozens of open invoices. Considering all possible combinations can get computationally overwhelming, but with the help of Excel's new **Insert Python**, we can streamline this process.
Here's how it works: we import the `itertools` library, leverage Python's powerful `combinations` method, and pass it a data frame of invoice amounts. The script then finds all possible combinations that sum up to the target value, and Excel seamlessly returns the results. Even better, you don't need to be paying for Copilot to utilize this technique—all you need is the latest version of **Microsoft 365**!
This solution runs incredibly fast compared to other methods, even when working with multiple invoices. And while there is a limit to how many combinations the free version of Python can handle, it still outperforms Excel's traditional formulas. If you're dealing with a tough reconciliation issue, download the workbook linked below and try it out yourself!
Enjoy the bonus Excel song at the end!![Musical note :musical_note: 🎵](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f3b5.png)
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Need Python but not Copilot
(0:30) Accounts Receivable - which invoice amounts add up to this check amount?
(0:54) Why problem is so complex - the combinations
(1:12) Use FILTER function to narrow the list of possible invoices
(1:47) Copy the filter results to a Ctrl+T Table
(2:00) 2 Python data frames to point to amounts
(2:40) Calling the Find Combinations function in Python
(3:01) Returning the data instead of a data frame
(3:11) Results are fast, even when changing check amount
(4:26) Works for 15 invoices, times-out for 27 invoices
(5:08) Excel Song: Finally Got The Check
This video answers these search terms:
Excel Python for invoice reconciliation
Solve knapsack problem in Excel
Find invoice combinations with Excel Python
Excel Copilot alternative for Python
Accounts receivable payment matching in Excel
Using Python in Excel for AR
Excel formulas with Python integration
Microsoft 365 Python invoice matching
Excel tips for payment reconciliation
Python itertools for sum combinations in Excel
To download the workbook from today: Excel Which Invoices Add Up To This Check Amount - 2658 Sample Files - MrExcel Publishing
Today, I'm excited to share a powerful solution to a problem I used to call the "knapsack problem" in accounts receivable. We often face scenarios where customers send a payment without specifying which invoices are covered—leaving us to solve the puzzle! In this video, I'll show you how to identify which invoices match a specific payment amount using Python in Excel, without the need for a Copilot subscription.
![Rocket :rocket: 🚀](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f680.png)
This challenge involves finding a combination of invoices that add up to the exact payment amount. For example, when we receive a check for **$54,967.28** from a customer, we need to match it against dozens of open invoices. Considering all possible combinations can get computationally overwhelming, but with the help of Excel's new **Insert Python**, we can streamline this process.
Here's how it works: we import the `itertools` library, leverage Python's powerful `combinations` method, and pass it a data frame of invoice amounts. The script then finds all possible combinations that sum up to the target value, and Excel seamlessly returns the results. Even better, you don't need to be paying for Copilot to utilize this technique—all you need is the latest version of **Microsoft 365**!
This solution runs incredibly fast compared to other methods, even when working with multiple invoices. And while there is a limit to how many combinations the free version of Python can handle, it still outperforms Excel's traditional formulas. If you're dealing with a tough reconciliation issue, download the workbook linked below and try it out yourself!
Enjoy the bonus Excel song at the end!
![Musical note :musical_note: 🎵](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f3b5.png)
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Need Python but not Copilot
(0:30) Accounts Receivable - which invoice amounts add up to this check amount?
(0:54) Why problem is so complex - the combinations
(1:12) Use FILTER function to narrow the list of possible invoices
(1:47) Copy the filter results to a Ctrl+T Table
(2:00) 2 Python data frames to point to amounts
(2:40) Calling the Find Combinations function in Python
(3:01) Returning the data instead of a data frame
(3:11) Results are fast, even when changing check amount
(4:26) Works for 15 invoices, times-out for 27 invoices
(5:08) Excel Song: Finally Got The Check
This video answers these search terms:
Excel Python for invoice reconciliation
Solve knapsack problem in Excel
Find invoice combinations with Excel Python
Excel Copilot alternative for Python
Accounts receivable payment matching in Excel
Using Python in Excel for AR
Excel formulas with Python integration
Microsoft 365 Python invoice matching
Excel tips for payment reconciliation
Python itertools for sum combinations in Excel
![maxresdefault.jpg](/board/proxy.php?image=https%3A%2F%2Fimg.youtube.com%2Fvi%2F6IohCo5KP9Q%2Fmaxresdefault.jpg&hash=0a33b94bf4cb2f146b7f9236a3f50192)
Transcript of the video:
Just down below the video, if you click Like..
That'll make sure that YouTube shows this video to more people - Thanks.
I'm blown away by this solution that I'm going to show you today.
This is what I used to call the knapsack problem, but maybe it's not a knapsack. And yes, I have the Copilot logo over here.
But the solution that I'm going to give you today, while I used Copilot to create it.
You do not need to be paying for Copilot to have this.
You do need to have on your Formulas tab, the insert Python group.
And if you do, then this is going to work. Okay, so here's our problem.
We work in accounts receivable. We just got a check in from this customer C.
You know that customer. They're a real pain.
They're a slow pay customer.
We have a bunch of open invoices from this customer. No note, no statement.
We don't know what they're paying. But we know it was a nice check.
54,967.28.
So the question is: From these invoices over here.
Which of them add up to exactly that amount - 54967.28?
The reason that this problem is so darn hard is if there's 47 invoices that we're considering. Two to the 47th is an insanely large number.
That's the number of combinations that can exist of those checks.
So we need to get that database as small as possible.
So here, enter which customer.
And then here I have a great little FILTER function.
Filter all of the invoices and amounts where the customer is equal to the customer that we received.
And that gets us down to two to the 15th, which is a doable amount.
Now if it's really that small, I also have a way to do this under Data, What-if Analysis, Data Table. That will have to be a topic for another day.
I think this method is far easier.
It doesn't require you to have a Copilot subscription.
But it does require you to be on the latest Microsoft 365.
And on the Formula tab to have the Insert Python group. The first thing I'm going to do.
I'm going to choose this data.
Control C and come over here and Paste Special Values. That way we have it in a Table.
Which Python is going to be able to ingest just a little bit easier here.
We want to insert Python, so that's Ctrl+Alt+Shift+P to create Python.
I'm going to make the formula bar larger so I can see what I'm doing.
Or I could go over to the Formula Editor. Alright, here's the first part of our code.
I'm going to import a library called itertools. It has something called Combinations.
I'm going to create two data frames.
The first data frame is the list of all the invoices that's pointing that table there in blue.
And then from this data frame, I'm going to get just the amounts columns.
So now I have a variable that has a list of all of the amounts for the amount I'm trying to match. I point to the invoice finder sheet and E8, right?
So that's the start of our code. Now here's where the magic happens.
And how do I find this code? Well, I asked Copilot.
And Copilot, wrote the code for me right here in Excel.
So it creates a function called Find Combinations. And then down here.
The Combinations Result is Find Combinations. These are the amounts.
So everything in column K. This is the target sum.
And then it's going to return the combinations result.
Now, I don't want that as a data frame. I want that to come back as an Excel value.
And when we're done, not just Enter, but Control Enter.
Normally when something takes a while, I actually cut it out and clip the video.
But instead look at how fast, look at how just insanely fast.
My other method using the What if table involves a lot of binary and at least 32,000 formulas.
It is pretty insane how long it takes to set this up before.
And Python just simply solves it.
Alright, so our goal here is to see if the sum that we're getting back from Python… Include a few extra cells.
…Is the same amount that we're searching for – 54967.28 and it matches.
Alright, let's just try another one. 67, 0 45.7 press enter.
The Python will recalculate. Look at that.
Look at how insanely fast this is. 65332.58.
And it finds the combination and matches. It's very nice, they are in sequence.
It's just easy enough to come here and say “that one matches”.
That one matches. 64.
And you just kind of look across here. And you will have the answer.
What an amazing, amazing tool. Download this workbook.
If you have Python in the formulas tab, you should be able to adapt this and it should work for you.
Now, hey look, this is funny. I've been throwing these little songs.
These little Excel songs in at the end.
And the song that I'm going to play at the end talks about having 27 invoices to choose from.
And when I try to throw 27 invoices at it, I got a TIMEOUT error.
So I don't know if that's because I'm not paying for the good version of Python. Alright, but 15 certainly worked.
27 gave me a timeout.
Somewhere between 15 and 27 is the upper limit of what you're going to do with the free Python that we have here in Excel.
I think it's pretty amazing.
Faster than the other method in case you don't have Python. I will record a video soon with the other method.
I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Yo, I’m in Accounts Receivable, grindin’ all day, Tryna track cash flow, make sure folks pay.
Got a slow-payin’ client, yeah, they love to delay, But today’s a big day—check came our way!
But wait, what’s this? No note, no clue. Which of the 27 invoices is it for? Who knew?
I’m lookin’ at the ledger, feelin’ the stress, Tryna match numbers like I’m playin’ chess.
Finally got the check, oh man, what a feelin’, But now I’m stuck calculatin’, sortin’, and dealin’.
No note, no details, just the amount to inspect, But MrExcel’s got the Python code to connect.
I used to spend hours, head in a bind, Searchin’ for sums that I couldn’t find.
But now I got the tools, Excel’s on my team, Python’s like a wizard, catchin’ the stream.
I plug in the check, run the script, hit play, It’s crunchin’ those numbers, clears up the dismay.
Matches line up, and I’m feelin’ free, No more late nights, thanks to that code sorcery.
Finally got the check, oh man, what a feelin’, But now I’m stuck calculatin’, sortin’, and dealin’.
No note, no details, just the amount to inspect, But MrExcel’s got the Python code to connect.
So if you’re stuck in the weeds, client bein’ vague, Don’t sweat, don’t stress, don’t let ‘em plague.
Excel and Python, the power duo in hand, Transformin’ my workload, I’m takin’ a stand.
Now the check’s reconciled, account’s lookin’ tight, Client’s in the clear, and I’m sleepin’ tonight.
Thanks to the combo, I’m back in control, AR superhero, yeah, I’m playin’ that role.
Finally got the check, oh man, what a feelin’, But now I’m not calculatin’, sortin’, or dealin’.
No note, no stress, it’s the Python effect, MrExcel’s got the fix, now who’s cashin’ respect?
That'll make sure that YouTube shows this video to more people - Thanks.
I'm blown away by this solution that I'm going to show you today.
This is what I used to call the knapsack problem, but maybe it's not a knapsack. And yes, I have the Copilot logo over here.
But the solution that I'm going to give you today, while I used Copilot to create it.
You do not need to be paying for Copilot to have this.
You do need to have on your Formulas tab, the insert Python group.
And if you do, then this is going to work. Okay, so here's our problem.
We work in accounts receivable. We just got a check in from this customer C.
You know that customer. They're a real pain.
They're a slow pay customer.
We have a bunch of open invoices from this customer. No note, no statement.
We don't know what they're paying. But we know it was a nice check.
54,967.28.
So the question is: From these invoices over here.
Which of them add up to exactly that amount - 54967.28?
The reason that this problem is so darn hard is if there's 47 invoices that we're considering. Two to the 47th is an insanely large number.
That's the number of combinations that can exist of those checks.
So we need to get that database as small as possible.
So here, enter which customer.
And then here I have a great little FILTER function.
Filter all of the invoices and amounts where the customer is equal to the customer that we received.
And that gets us down to two to the 15th, which is a doable amount.
Now if it's really that small, I also have a way to do this under Data, What-if Analysis, Data Table. That will have to be a topic for another day.
I think this method is far easier.
It doesn't require you to have a Copilot subscription.
But it does require you to be on the latest Microsoft 365.
And on the Formula tab to have the Insert Python group. The first thing I'm going to do.
I'm going to choose this data.
Control C and come over here and Paste Special Values. That way we have it in a Table.
Which Python is going to be able to ingest just a little bit easier here.
We want to insert Python, so that's Ctrl+Alt+Shift+P to create Python.
I'm going to make the formula bar larger so I can see what I'm doing.
Or I could go over to the Formula Editor. Alright, here's the first part of our code.
I'm going to import a library called itertools. It has something called Combinations.
I'm going to create two data frames.
The first data frame is the list of all the invoices that's pointing that table there in blue.
And then from this data frame, I'm going to get just the amounts columns.
So now I have a variable that has a list of all of the amounts for the amount I'm trying to match. I point to the invoice finder sheet and E8, right?
So that's the start of our code. Now here's where the magic happens.
And how do I find this code? Well, I asked Copilot.
And Copilot, wrote the code for me right here in Excel.
So it creates a function called Find Combinations. And then down here.
The Combinations Result is Find Combinations. These are the amounts.
So everything in column K. This is the target sum.
And then it's going to return the combinations result.
Now, I don't want that as a data frame. I want that to come back as an Excel value.
And when we're done, not just Enter, but Control Enter.
Normally when something takes a while, I actually cut it out and clip the video.
But instead look at how fast, look at how just insanely fast.
My other method using the What if table involves a lot of binary and at least 32,000 formulas.
It is pretty insane how long it takes to set this up before.
And Python just simply solves it.
Alright, so our goal here is to see if the sum that we're getting back from Python… Include a few extra cells.
…Is the same amount that we're searching for – 54967.28 and it matches.
Alright, let's just try another one. 67, 0 45.7 press enter.
The Python will recalculate. Look at that.
Look at how insanely fast this is. 65332.58.
And it finds the combination and matches. It's very nice, they are in sequence.
It's just easy enough to come here and say “that one matches”.
That one matches. 64.
And you just kind of look across here. And you will have the answer.
What an amazing, amazing tool. Download this workbook.
If you have Python in the formulas tab, you should be able to adapt this and it should work for you.
Now, hey look, this is funny. I've been throwing these little songs.
These little Excel songs in at the end.
And the song that I'm going to play at the end talks about having 27 invoices to choose from.
And when I try to throw 27 invoices at it, I got a TIMEOUT error.
So I don't know if that's because I'm not paying for the good version of Python. Alright, but 15 certainly worked.
27 gave me a timeout.
Somewhere between 15 and 27 is the upper limit of what you're going to do with the free Python that we have here in Excel.
I think it's pretty amazing.
Faster than the other method in case you don't have Python. I will record a video soon with the other method.
I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Yo, I’m in Accounts Receivable, grindin’ all day, Tryna track cash flow, make sure folks pay.
Got a slow-payin’ client, yeah, they love to delay, But today’s a big day—check came our way!
But wait, what’s this? No note, no clue. Which of the 27 invoices is it for? Who knew?
I’m lookin’ at the ledger, feelin’ the stress, Tryna match numbers like I’m playin’ chess.
Finally got the check, oh man, what a feelin’, But now I’m stuck calculatin’, sortin’, and dealin’.
No note, no details, just the amount to inspect, But MrExcel’s got the Python code to connect.
I used to spend hours, head in a bind, Searchin’ for sums that I couldn’t find.
But now I got the tools, Excel’s on my team, Python’s like a wizard, catchin’ the stream.
I plug in the check, run the script, hit play, It’s crunchin’ those numbers, clears up the dismay.
Matches line up, and I’m feelin’ free, No more late nights, thanks to that code sorcery.
Finally got the check, oh man, what a feelin’, But now I’m stuck calculatin’, sortin’, and dealin’.
No note, no details, just the amount to inspect, But MrExcel’s got the Python code to connect.
So if you’re stuck in the weeds, client bein’ vague, Don’t sweat, don’t stress, don’t let ‘em plague.
Excel and Python, the power duo in hand, Transformin’ my workload, I’m takin’ a stand.
Now the check’s reconciled, account’s lookin’ tight, Client’s in the clear, and I’m sleepin’ tonight.
Thanks to the combo, I’m back in control, AR superhero, yeah, I’m playin’ that role.
Finally got the check, oh man, what a feelin’, But now I’m not calculatin’, sortin’, or dealin’.
No note, no stress, it’s the Python effect, MrExcel’s got the fix, now who’s cashin’ respect?