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

28 thoughts on “Populating New list for Advance Set off against Invoices in VBA Excel

  1. brilliantlighttherapy

    I and my guys were viewing the nice secrets on your site while quickly developed a horrible suspicion I never expressed respect to the web blog owner for them. My people happened to be for that reason passionate to learn them and now have surely been using these things. I appreciate you for actually being so considerate and also for selecting varieties of outstanding tips millions of individuals are really eager to discover. Our own honest regret for not saying thanks to sooner.

  2. Acheter Salbutamol

    I haven’t checked in here for a while since I thought it was getting boring, but the last few posts are great quality so I guess I’ll add you back to my daily bloglist. You deserve it my friend 🙂

  3. Acquistare Testosterone propionate

    Hello there, just became aware of your weblog through Google, and located that it is truly informative. I am going to watch out for brussels. I will be grateful when you proceed this in future. Numerous folks might be benefited from your writing. Cheers!

  4. Acheter T3 liothyronine

    Hi! This post couldn’t be written any better! Reading through this post reminds me of my previous room mate! He always kept talking about this. I will forward this page to him. Pretty sure he will have a good read. Thank you for sharing!

  5. anabole steroide bestellen

    Excellent goods from you, man. I have understand your stuff previous to and you are just too wonderful. I really like what you have acquired here, really like what you are stating and the way in which you say it. You make it enjoyable and you still take care of to keep it wise. I can not wait to read far more from you. This is really a wonderful website.

  6. potenzmittel online kaufen

    Can I simply say what a relief to search out somebody who actually knows what theyre talking about on the internet. You positively know methods to bring an issue to mild and make it important. More people need to read this and perceive this side of the story. I cant consider youre no more standard since you positively have the gift.

  7. Acquistare Reductil in linea

    Once I initially commented I clicked the -Notify me when new feedback are added- checkbox and now each time a comment is added I get 4 emails with the identical comment. Is there any manner you’ll be able to remove me from that service? Thanks!

  8. Acheter Phentermine

    I must show thanks to the writer for bailing me out of this particular situation. After surfing around through the the web and finding basics that were not beneficial, I believed my entire life was over. Existing minus the solutions to the difficulties you have resolved as a result of your short post is a crucial case, and ones that could have badly affected my career if I had not discovered your web blog. Your ability and kindness in touching every item was helpful. I am not sure what I would’ve done if I had not come across such a stuff like this. I am able to at this moment relish my future. Thanks for your time so much for this high quality and sensible guide. I won’t think twice to recommend the sites to any individual who wants and needs direction on this topic.

  9. Acquista Reductil online

    Awesome blog you have here but I was curious if you knew of any discussion boards that cover the same topics talked about in this article? I’d really like to be a part of community where I can get suggestions from other experienced people that share the same interest. If you have any suggestions, please let me know. Many thanks!

  10. acheter steroides en ligne

    Definitely believe that which you said. Your favorite justification seemed to be on the internet the easiest thing to be aware of. I say to you, I certainly get annoyed while people think about worries that they plainly do not know about. You managed to hit the nail upon the top and also defined out the whole thing without having side-effects , people could take a signal. Will likely be back to get more. Thanks

  11. Ephedrine online bestellen

    Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a bit, but instead of that, this is fantastic blog. An excellent read. I will definitely be back.

  12. ig

    I’m now not positive the place you are getting your information, however great topic.

    I must spend some time finding out much more or understanding more.

    Thank you for great info I was in search of this information for
    my mission.

  13. Monnie Rothschild

    With every thing which appears to be building inside this particular area, your opinions tend to be very exciting. On the other hand, I beg your pardon, because I do not subscribe to your whole plan, all be it exciting none the less. It would seem to everyone that your comments are generally not entirely justified and in actuality you are generally your self not totally confident of the argument. In any case I did enjoy reading it.

  14. why not find out more

    I was very pleased to find this web-site.I wanted to thanks for your time for this wonderful read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you blog post.

  15. Buy Letter Vitamins A-K

    Its such as you learn my thoughts! You seem to know so much about this, such as you wrote the e book in it or something. I think that you can do with some p.c. to drive the message house a little bit, however instead of that, this is excellent blog. An excellent read. I’ll definitely be back.

  16. Samara Borer

    It’s really a great and useful piece of information. I am glad that you shared this useful info with us. Please keep us informed like this. Thanks for sharing.

Comments are closed.