A semi-advanced matching problem which may be solved with formulization or VBA

[The Godfather]

Dear excel masters,

I have a task to match all vendor invoices with their related payments with FIFO logic. However I cannot build the model in excel and need assistance.

As you can see in data chunk (or screenshot); there are 3 clearing items in data (in my actual data more than 5.000) as seen in B column.

- In clearing 111111; we see 5.000 USD invoice as of 11.11.2022 is paid with 5.000 USD on 30.12.2022.
- However not every clearing consists up of only one invoice and one payment. Such a in clearing 3332222; we see 3 different invoices, where invoice no.s 17 and 18 (total 10 K USD) is paid with payment 32. And Invoice 19 is fully paid with payment 36.
- Sometimes invoice and payments are not directly matched in amount, and some portion of invoices might not have been paid yet. In clearing 111122, we see that,
+ payment 131 (30 K USD) fully pays invoice 112 (15 K USD), and then pays 15 K portion of invoice 114.
+ for invoice 114, the remaining 5 K USD amount is paid by payment 134.
+ With FIFO method; when all invoices and payments are matched; we still see that invoice 131 have a 5 K USD portion which have not yet been paid.

What I am trying to do?
For each invoice; I am trying to obtain what part of amount is paid as of which date; and to see if there is any remaining invoice amount not yet been paid.
How can I construct a model which solves this?

Thanks in advance.

Vendor	Clearing Reference No	Document No	Document Type	Document Date	Amount in USD	Payment Matching Amount	Payment Matching Date	Comments
SSSS GmbH	111111	15	Invoice	11.11.2022​	5.000​	5.000​	30.12.2022​	fully paid by document 25
SSSS GmbH	111111	25	Payment	30.12.2022​	-5.000​
XYZ Ltd.	3332222	17	Invoice	12.12.2022​	4.000​	4.000​	20.12.2022​	fully paid by document 32
XYZ Ltd.	3332222	18	Invoice	13.12.2022​	6.000​	6.000​	20.12.2022​	fully paid by document 32
XYZ Ltd.	3332222	19	Invoice	14.12.2022​	2.000​	2.000​	25.12.2022​	fully paid by document 36
XYZ Ltd.	3332222	32	Payment	20.12.2022​	-10.000​
XYZ Ltd.	3332222	36	Payment	25.12.2022​	-2.000​
ABC Company	111122	112	Invoice	24.10.2022​	15.000​	15.000​	29.11.2022​	fully paid by document 131
ABC Company	111122	114	Invoice	24.10.2022​	20.000​	15.000​	29.11.2022​	15.000 portion paid by document 131
						5.000​	01.12.2022​	remaining 5.000 portion paid by document 134
ABC Company	111122	117	Invoice	24.10.2022​	12.000​	12.000​	01.12.2022​	fully paid by document 134
ABC Company	111122	118	Invoice	24.10.2022​	9.000​	9.000​	01.12.2022​	fully paid by document 134
ABC Company	111122	119	Invoice	24.10.2022​	13.000​	13.000​	01.12.2022​	fully paid by document 134
ABC Company	111122	121	Invoice	30.11.2022​	11.000​	6.000​	01.12.2022​	6.000 USD portion paid by document 134. Remaining 5.000 USD portion has not yet been paid.
ABC Company	111122	131	Payment	29.11.2022​	-30.000​
ABC Company	111122	134	Payment	01.12.2022​	-45.000​

EDIT:
I would like to renew my purpose;
I am just trying to automatically fill the two columns below;
- Payment Matching Amount
- Payment Matching Date

(Also as seen in invoice document 114; sometimes the information may be in more than one line since the invoice is paid with different payments).

I am not interested to find remaining invoice amount that have not yet been filled.

 

[The Godfather]

Let me paraphrase the issue in a more clear way;

Invoices table

ClearingDocument	DocumentNo	DocType	DocDate	Amount
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

ClearingDocument	DocumentNo	DocType	DocDate	Amount
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;

ClearingDocument	DocumentNo	DocType	DocDate	Amount	InvoiceAmountPaid	PaymentDate	PaymentDocNo
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.