+ Reply to Thread
Results 1 to 7 of 7

Matching cheque payments to invoices

  1. #1
    Eddie
    Guest

    Matching cheque payments to invoices

    Hi, please can somebody help!


    I have 1000 unidentifed cheque payments from the bank that relate to a
    number of invoices. I am trying to match the payments to the invoices. The
    combinations are endless. I have tried the office assistant but this has
    yielded no results. I am doing this Microsoft excel 2000.

    for example

    cheque payments:
    $137.37
    $258.36
    $566.52 etc etc

    Thanks
    I want to know which, if combined or on their add up to say, $9800

  2. #2
    bj
    Guest

    RE: Matching cheque payments to invoices

    Since there are about 10 (with 300 zeros) distinct combinations of 1000
    items, No brute force method will be appropriate.
    The first thing to do is to shink down the possible number of combinations
    about how many invoices are there?
    what type of ranges do you have for the invoices and the payments?
    Is there any max or min number of payments which would be associated with
    any invoice?
    do you know how many payments were made on any invoice?
    One difficulty that you will have is that with these many items, there will
    probably be multiple possible solutions for any individual invoice. and there
    may be multiples of all included solutions.

    "Eddie" wrote:

    > Hi, please can somebody help!
    >
    >
    > I have 1000 unidentifed cheque payments from the bank that relate to a
    > number of invoices. I am trying to match the payments to the invoices. The
    > combinations are endless. I have tried the office assistant but this has
    > yielded no results. I am doing this Microsoft excel 2000.
    >
    > for example
    >
    > cheque payments:
    > $137.37
    > $258.36
    > $566.52 etc etc
    >
    > Thanks
    > I want to know which, if combined or on their add up to say, $9800


  3. #3
    Eddie
    Guest

    RE: Matching cheque payments to invoices

    Hi bj, thank you for your prompt reply. The problem I have is I don't know
    how many payments relate to a particular invoice. If you can forward a
    formula of some sort for this problem, I maybe able to amend it for my own
    purposes. Thanks

    "bj" wrote:

    > Since there are about 10 (with 300 zeros) distinct combinations of 1000
    > items, No brute force method will be appropriate.
    > The first thing to do is to shink down the possible number of combinations
    > about how many invoices are there?
    > what type of ranges do you have for the invoices and the payments?
    > Is there any max or min number of payments which would be associated with
    > any invoice?
    > do you know how many payments were made on any invoice?
    > One difficulty that you will have is that with these many items, there will
    > probably be multiple possible solutions for any individual invoice. and there
    > may be multiples of all included solutions.
    >
    > "Eddie" wrote:
    >
    > > Hi, please can somebody help!
    > >
    > >
    > > I have 1000 unidentifed cheque payments from the bank that relate to a
    > > number of invoices. I am trying to match the payments to the invoices. The
    > > combinations are endless. I have tried the office assistant but this has
    > > yielded no results. I am doing this Microsoft excel 2000.
    > >
    > > for example
    > >
    > > cheque payments:
    > > $137.37
    > > $258.36
    > > $566.52 etc etc
    > >
    > > Thanks
    > > I want to know which, if combined or on their add up to say, $9800


  4. #4
    RagDyeR
    Guest

    Re: Matching cheque payments to invoices

    See if this old post of Ken Wright's can help:

    http://tinyurl.com/a82os
    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================


    "Eddie" <Eddie@discussions.microsoft.com> wrote in message
    news:0DC82071-B096-419A-8309-6013FCCD6C87@microsoft.com...
    Hi bj, thank you for your prompt reply. The problem I have is I don't know
    how many payments relate to a particular invoice. If you can forward a
    formula of some sort for this problem, I maybe able to amend it for my own
    purposes. Thanks

    "bj" wrote:

    > Since there are about 10 (with 300 zeros) distinct combinations of 1000
    > items, No brute force method will be appropriate.
    > The first thing to do is to shink down the possible number of combinations
    > about how many invoices are there?
    > what type of ranges do you have for the invoices and the payments?
    > Is there any max or min number of payments which would be associated with
    > any invoice?
    > do you know how many payments were made on any invoice?
    > One difficulty that you will have is that with these many items, there

    will
    > probably be multiple possible solutions for any individual invoice. and

    there
    > may be multiples of all included solutions.
    >
    > "Eddie" wrote:
    >
    > > Hi, please can somebody help!
    > >
    > >
    > > I have 1000 unidentifed cheque payments from the bank that relate to a
    > > number of invoices. I am trying to match the payments to the invoices.

    The
    > > combinations are endless. I have tried the office assistant but this

    has
    > > yielded no results. I am doing this Microsoft excel 2000.
    > >
    > > for example
    > >
    > > cheque payments:
    > > $137.37
    > > $258.36
    > > $566.52 etc etc
    > >
    > > Thanks
    > > I want to know which, if combined or on their add up to say, $9800




  5. #5
    bj
    Guest

    RE: Matching cheque payments to invoices

    Unfortunately I don't think there will be a formula as a response.
    What I would try first is to look at the largest payment. hopefully it will
    be larger than some of the invoices. By looking at the difference between
    this payment and the invoice values, You may be able to eliminate some of
    the invoices because the difference is not within the range of the ramaining
    payments.

    With out something to limit the number of combinations, I don't have a high
    confidence You will be able to do it. sorry.

    "Eddie" wrote:

    > Hi bj, thank you for your prompt reply. The problem I have is I don't know
    > how many payments relate to a particular invoice. If you can forward a
    > formula of some sort for this problem, I maybe able to amend it for my own
    > purposes. Thanks
    >
    > "bj" wrote:
    >
    > > Since there are about 10 (with 300 zeros) distinct combinations of 1000
    > > items, No brute force method will be appropriate.
    > > The first thing to do is to shink down the possible number of combinations
    > > about how many invoices are there?
    > > what type of ranges do you have for the invoices and the payments?
    > > Is there any max or min number of payments which would be associated with
    > > any invoice?
    > > do you know how many payments were made on any invoice?
    > > One difficulty that you will have is that with these many items, there will
    > > probably be multiple possible solutions for any individual invoice. and there
    > > may be multiples of all included solutions.
    > >
    > > "Eddie" wrote:
    > >
    > > > Hi, please can somebody help!
    > > >
    > > >
    > > > I have 1000 unidentifed cheque payments from the bank that relate to a
    > > > number of invoices. I am trying to match the payments to the invoices. The
    > > > combinations are endless. I have tried the office assistant but this has
    > > > yielded no results. I am doing this Microsoft excel 2000.
    > > >
    > > > for example
    > > >
    > > > cheque payments:
    > > > $137.37
    > > > $258.36
    > > > $566.52 etc etc
    > > >
    > > > Thanks
    > > > I want to know which, if combined or on their add up to say, $9800


  6. #6
    Eddie
    Guest

    Re: Matching cheque payments to invoices

    Hi, thank you for your help. I am trying to work through the link that you
    kindly forwarded
    http://tinyurl.com/a82os. I am stilling trying to solve my problem, the
    solution that is given is the one that I require. Is it possible you could
    forward an excel sheet with a made up example going through the solution? I
    think this will be very beneficial. Thanks and hope to hear from you soon.
    thanks


    "RagDyeR" wrote:

    > See if this old post of Ken Wright's can help:
    >
    > http://tinyurl.com/a82os
    > --
    >
    > HTH,
    >
    > RD
    > ==============================================
    > Please keep all correspondence within the Group, so all may benefit!
    > ==============================================
    >
    >
    > "Eddie" <Eddie@discussions.microsoft.com> wrote in message
    > news:0DC82071-B096-419A-8309-6013FCCD6C87@microsoft.com...
    > Hi bj, thank you for your prompt reply. The problem I have is I don't know
    > how many payments relate to a particular invoice. If you can forward a
    > formula of some sort for this problem, I maybe able to amend it for my own
    > purposes. Thanks
    >
    > "bj" wrote:
    >
    > > Since there are about 10 (with 300 zeros) distinct combinations of 1000
    > > items, No brute force method will be appropriate.
    > > The first thing to do is to shink down the possible number of combinations
    > > about how many invoices are there?
    > > what type of ranges do you have for the invoices and the payments?
    > > Is there any max or min number of payments which would be associated with
    > > any invoice?
    > > do you know how many payments were made on any invoice?
    > > One difficulty that you will have is that with these many items, there

    > will
    > > probably be multiple possible solutions for any individual invoice. and

    > there
    > > may be multiples of all included solutions.
    > >
    > > "Eddie" wrote:
    > >
    > > > Hi, please can somebody help!
    > > >
    > > >
    > > > I have 1000 unidentifed cheque payments from the bank that relate to a
    > > > number of invoices. I am trying to match the payments to the invoices.

    > The
    > > > combinations are endless. I have tried the office assistant but this

    > has
    > > > yielded no results. I am doing this Microsoft excel 2000.
    > > >
    > > > for example
    > > >
    > > > cheque payments:
    > > > $137.37
    > > > $258.36
    > > > $566.52 etc etc
    > > >
    > > > Thanks
    > > > I want to know which, if combined or on their add up to say, $9800

    >
    >
    >


  7. #7
    RagDyeR
    Guest

    Re: Matching cheque payments to invoices

    I've never had occasion to use "Solver" myself, so I wouldn't be much help.

    Check it out in the XL Help files, and you might look into these MS help
    files concerning Solver:

    http://tinyurl.com/a7txq
    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================


    "Eddie" <Eddie@discussions.microsoft.com> wrote in message
    news:488F39AE-7827-4B83-B6F7-BB5CCA196E3E@microsoft.com...
    Hi, thank you for your help. I am trying to work through the link that you
    kindly forwarded
    http://tinyurl.com/a82os. I am stilling trying to solve my problem, the
    solution that is given is the one that I require. Is it possible you could
    forward an excel sheet with a made up example going through the solution? I
    think this will be very beneficial. Thanks and hope to hear from you soon.
    thanks


    "RagDyeR" wrote:

    > See if this old post of Ken Wright's can help:
    >
    > http://tinyurl.com/a82os
    > --
    >
    > HTH,
    >
    > RD
    > ==============================================
    > Please keep all correspondence within the Group, so all may benefit!
    > ==============================================
    >
    >
    > "Eddie" <Eddie@discussions.microsoft.com> wrote in message
    > news:0DC82071-B096-419A-8309-6013FCCD6C87@microsoft.com...
    > Hi bj, thank you for your prompt reply. The problem I have is I don't

    know
    > how many payments relate to a particular invoice. If you can forward a
    > formula of some sort for this problem, I maybe able to amend it for my own
    > purposes. Thanks
    >
    > "bj" wrote:
    >
    > > Since there are about 10 (with 300 zeros) distinct combinations of 1000
    > > items, No brute force method will be appropriate.
    > > The first thing to do is to shink down the possible number of

    combinations
    > > about how many invoices are there?
    > > what type of ranges do you have for the invoices and the payments?
    > > Is there any max or min number of payments which would be associated

    with
    > > any invoice?
    > > do you know how many payments were made on any invoice?
    > > One difficulty that you will have is that with these many items, there

    > will
    > > probably be multiple possible solutions for any individual invoice. and

    > there
    > > may be multiples of all included solutions.
    > >
    > > "Eddie" wrote:
    > >
    > > > Hi, please can somebody help!
    > > >
    > > >
    > > > I have 1000 unidentifed cheque payments from the bank that relate to a
    > > > number of invoices. I am trying to match the payments to the

    invoices.
    > The
    > > > combinations are endless. I have tried the office assistant but this

    > has
    > > > yielded no results. I am doing this Microsoft excel 2000.
    > > >
    > > > for example
    > > >
    > > > cheque payments:
    > > > $137.37
    > > > $258.36
    > > > $566.52 etc etc
    > > >
    > > > Thanks
    > > > I want to know which, if combined or on their add up to say, $9800

    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1