Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#5
|
|||
|
|||
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
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I forecast future payments by analyzing past payments? | Excel Worksheet Functions | |||
Need help determining # of invoices | Excel Discussion (Misc queries) | |||
Pivot Tables - "simple" question | Excel Discussion (Misc queries) | |||
Invoices | Setting up and Configuration of Excel | |||
How to calculate total interest on 12 month loan with early payments | Excel Worksheet Functions |