# Matching cheque payments to invoices

1. ## Matching cheque payments to invoices

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

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. ## 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
>
> 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. ## 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. ## 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. ## 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. ## 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. ## 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

>
>
>

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

#### 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