1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Invoice and Payment matching

Discussion in 'Ask an Excel Question' started by Sagduel, Apr 16, 2018.

  1. Sagduel

    Sagduel New Member

    Messages:
    3
    Hi,

    Presently i am working on data that seems like this which relates to payment
    Payment dateDocument no.AmountInterestDue date
    12-Apr-171204011000010001-Apr-2017
    10-May-171204022000020001-Apr-2017
    30-Jun-1712040310000100030-May-17








    Now we have linked this payments based on the FIFO basis for the the invoice where the following are the detailed present in invoice.
    Invoice dateInvoice NumberAmountDue Date
    1-Mar-2017120301300001-Apr-2017
    1-May-20171205011500030-May-2017

    Data which i Have
    Involves debit and credit (Positive Payment) Negative (Invoice) in single sheet and if we total up debit and credit the sum=0. I.e i have all the invoice and payments matched data.

    What i require is For which invoice the payment has been matched and how many times the payment is adjusted to the invoice and how much interest is charged against which invoice.

    The due date is the only constant figure here as there is no other reference.,

    Challenges faced while doing this excercise.
    1. Multiple invoice contains same due dates.
    2. Payments are made in multiples for single invoice.
    3. Payments are adjusted to multiple invoices.

    It's urgent Kindly provide any idea.

    Presently we are giving reference number manually for all the payments and invoice. Where we have 60000 above line items in one month as it will take more than 6 months of manual reference for doing one year matching.

    I have attached the document

    Attached Files:

  2. Deepak

    Deepak Excel Ninja

    Messages:
    2,839
    It's not a challenge as seems query to me.
  3. Sagduel

    Sagduel New Member

    Messages:
    3
    Then solve it lets see
  4. Deepak

    Deepak Excel Ninja

    Messages:
    2,839
    Do you have already solution for this?
  5. Sagduel

    Sagduel New Member

    Messages:
    3
    Yes... But it was done manually by matching all the invoices u can see the sheet attached 1st sheet is the solution 2nd sheet is the query
  6. Deepak

    Deepak Excel Ninja

    Messages:
    2,839
    Then It's not a challenge anyhow. Have wait and one will revert after getting it.
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    Hmmm, it seems sheet 1 contains the solution of vendor 100223, whereas the data is all about vendor 100212.
    Perhaps upload a file where data is at least consistent if you like,Sagduel.
    Chandoo did blog about a very similar issue and he solved it via a pivots. I admit you seem to have a bit more complexity, but the basics idea can be used.
    I think it is this one
    https://chandoo.org/wp/2014/06/10/matching-transactions-pivot-tables/

Share This Page