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

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

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

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" wrote in message
...
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

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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