Matching invoice amounts and payment amounts

The Godfather

New Member
Joined
Jul 22, 2011
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear all,

I have a task to match invoice and payments related to the same clearing documents with FIFO method. I tried it with Excel however could not succeed. MS Access might be the solution however I have quite limited SQL experience. A very simple dataset consists up of invoices and payments is this;

Invoices table
ClearingDocumentDocumentNoDocTypeDocDateAmount
111​
1​
Invoice
24.10.2022​
25.000​
111​
2​
Invoice
25.10.2022​
25.000​
111​
3​
Invoice
26.10.2022​
25.000​
111​
4​
Invoice
27.10.2022​
25.000​
222​
13​
Invoice
27.10.2022​
7.500​

Payments table
ClearingDocumentDocumentNoDocTypeDocDateAmount
111​
7​
Payment
01.01.2023​
50.000​
111​
8​
Payment
01.02.2023​
30.000​
111​
9​
Payment
01.03.2023​
30.000​
222​
15​
Payment
02.03.2023​
7.500​


And the desired outcome is to obtain which portion of invoice is paid as of which date. Like this;

ClearingDocumentDocumentNoDocTypeDocDateAmountInvoiceAmountPaidPaymentDatePaymentDocNo
111​
1​
Invoice
24.10.2022​
25.000​
25.000​
01.01.2023​
7​
111​
2​
Invoice
25.10.2022​
25.000​
25.000​
01.01.2023​
7​
111​
3​
Invoice
26.10.2022​
25.000​
25.000​
01.02.2023​
8​
111​
4​
Invoice
27.10.2022​
25.000​
5.000​
01.02.2023​
8​
111​
4​
Invoice
27.10.2022​
25.000​
20.000​
01.03.2023​
9​
222​
13​
Invoice
27.10.2022​
7.500​
7.500​
02.03.2023​
15​

As you may see in the desired outcome,

1) For clearing no. 222; since there is only one invoice and there is only one payment which are matching in amount (7.500); the result is so smooth.
2) For clearing no. 111; it is a bit more complicated.
a) Invoice 1 and 2 (each 25 K) was paid with payment 7 (50 K). In the desired outcome we see that half of payment 7 (25 K) is attribute to invoice 1; and the other half is attributed to invoice 2.
b) As per FIFO, Payment 8 (30 K) first pays invoice 3 (25 K), and then pays the 5K portion of invoice 4. The remaining of invoice 4 is paid by payment 9.

So basicly; for under each clearing item and based on FIFO; I am trying to match invoices and payments according to the two rules I mentioned above.
It does not matter if the total amount of invoices and total amount of payments under the same clearing chunk is not the same (ie; does not result in 0). The code should run until either total invoice or total payment amount in that chunk runs to 0.

However I cannot formulate it in SQL.
Any help is appreciated.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top