Populating New list for Advance Set off against Invoices in VBA Excel

I have 2 table in excel, 1 have to Vendor’s Invoice details and other have Advance given to them, like below

VENDOR_NAME   Supplier Code INVOICE_DATE    INVOICE_NUM    INVOICE_AMOUNT
A.N.Kothari & Co.   1291    31-03-2016  Mig_Inv_368         2090
A.N.Kothari & Co.   1291    31-03-2016  Mig_Inv_375         2090
A.N.Kothari & Co.   1291    31-03-2016  Mig_Inv_376         2090
A.N.Kothari & Co.   1291    31-03-2016  Mig_Inv_378         2090
A.s. Vasyan         1293    20-05-2016  1055/2016-17        2290
A.s. Vasyan         1293    05-01-2017  227/2016-17         5750

Advance or Payment Table are as below

VENDOR_NAME Supplier Code Advance Date  Ref No  Advance Amount
A.N.Kothari & Co.   1291    18-Apr-16   adv-177     4315
A.N.Kothari & Co.   1291    06-May-16   adv-954     2090
A.s. Vasyan         1293    30-Jan-17   adv-1052    10000
A.s. Vasyan         1293    31-Jan-17   adv-1053    10000

On the basis of these 2 tables I want to populate a table, where invoice are set off against advance, giving reference no of both Invoice and Advance. Obviously if Advance is more than Total Invoices then All Invoices should be set off and if Advance is less, Invoices should be set off on FIFO basis. Like given below in table.

VENDOR_NAME   Supplier Code advance ref INVOICE_NUM    INVOICE_AMOUNT
A.N.Kothari & Co.   1291    adv-177     Mig_Inv_368         2090
A.N.Kothari & Co.   1291    adv-177     Mig_Inv_375         2090
A.N.Kothari & Co.   1291    adv-177     Mig_Inv_376         135
A.N.Kothari & Co.   1291    adv-954     Mig_Inv_376         1955
A.N.Kothari & Co.   1291    adv-954     Mig_Inv_378         135
A.s. Vasyan         1293    adv-1052    1055/2016-17        2290
A.s. Vasyan         1293    adv-1052    227/2016-17         5750

As you can see Invoice No Mig_Inv_376 has to be split in 2 rows to set off against 2 advance, as first advance was not enough as it was already utilized in previous Invoices.

I have tried really hard from last 3-4 hours but only thing i am getting is scratching my head. Advance thanks if someone really help me out.

By: Satyendra

Leave a Reply

Your email address will not be published. Required fields are marked *