Microsoft Excel Tutorial: Which Invoices Add Up To This Check Amount – Faster method using Python in Excel.

To download the workbook from today: https://www.mrexcel.com/youtube/6IohCo5KP9Q/

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. 🚀

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! 🎵

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

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