Excel VBA Payment Reconciliation

This is probably too complicated and beyond the scope of this forum, but I figured I'd give it a shot anyway. I won't be (too) offended if you tell me to screw off .

Anyways, I am attempting to create a report that reconciles a Customer's payment against a list of their open invoices. Most customers are helpful and email us a copy of their remittance advice with their ACH or Wire payments that explains the exact breakdown of their payment. Others, not so much.

Currently this is a multi-step process where I've already done the prep work of everything up to the reconciliation portion.

1. User enters a Customer Number into a named range on the worksheet
2. This triggers a worksheet event that performs a simple ADODB SQL Update into an MS Access database. It just updates a micro table with a single field and single record so another query can perform a LEFT JOIN against it (since Excel's Power Query is incapable of passing parameters to Access - what a shame).
3. The same event then refreshes/updates a Power Query that is linked to the same Access DB. The query in Access is connected to our ERP database via an ODBC connection. It returns a complete list of every open invoice (based on Balance<>0) for the provided customer number that we just updated the micro table in Access with.
4. The Power Query is loaded to the worksheet as 'OpenInvoiceLookup' and the ListObject's name is the same.

Now here's the fun part that is well beyond my capabilities.

First of all, understand that not all customers are created equal - and this translates to much additional complexity.
The goal is for the user to be able to enter the payment amount into another named range in Excel that would trigger another workbook change event. This is where I would need to start matching potential invoices against the customer's payment.

Here's a sample of what the table would look like:
Attachment 854736

Now the part where I said not all customers are created equal...
Most customers pay the balance due.
There are some who short-pay because they're tax exempt and haven't sent in their tax exemption to us or failed to input the freight amount into their system.
Then there are some customers who pay early and take their prompt-Pay discount. Then there are some customers who pay late and still take their prompt-pay discount.
And finally we have customers who can be any of these combinations of things.

Note: The discount isn't included in the customer's balance due. Some customers who pay within their discount period also do not always take their discount.

Essentially I am looking for a method to figure out what the customer may have paid down to the penny. For each record they could have paid:

• The [Balance] (most likely)
• The [Balance] - [DiscountAmt]
• The [Balance] - [SalesTaxAmt]
• The [Balance] - [FreightAmt]
• The [Balance] - [SalesTaxAmt] - [DiscountAmt]
• The [Balance] - [FreightAmt] - [DiscountAmt]
• There are technically a few more combinations, but those would be the least likely and far edge cases.

It would be nice to perform a check on just the Balance first as these are the most common, then to perhaps take a wide view of the other possibilities.

Also if it is helpful for me to load the table data in a different way, I can modify the linked Access Query to output data however it's beneficial (for example, if it would be helpful for me to include columns for [BalanceLessDisc] and [BalanceLessTax], etc) then I am all for it.

Also noted: I wouldn't mind a Power Query or VBA solution, although while knowing PQ is pretty powerful in transforming data I can't even be certain if it's up for the task of this complexity but I would love to see the inter-workings of the involved steps if I am wrong.



I sincerely appreciate the brave souls who are willing to throw some attempts at this.




I've included 2 workbooks that are identical with the exception of one has the macros from the original and one without.
The VBA code in the macro-enabled workbook isn't essential to the request and and non-vba workbook can be used, but I went ahead and included both for the sake of why not.