Need a macro

Is there a limit to the number of entries, or could it be any number of entries add up to the disbursement amount?

Can you give an example of how your data is organized in your sheet?

Lori,

The short answer is, yes, it is possible to create such a macro.

Unfortunately there is a long answer, and it has two parts:

1) in general, the solution to this problem is not unique, i.e., there may be many different combinations of that add up to the total. Thus, you may have A solution, but not THE solution (the actual disbursements that cleared).

2) because of the potentially astronomical number of combinations of disbursements, an algorithm that exhaustively looks at every combination to try to find one that provides the correct total when hundreds or thousands of checks are involved is probably not practical--it might take longer than the lifetime of the universe to run even on a Pentium XXVII (I don't know about you, but I'm not willing to wait that long). An efficient algorithm is therefore needed, and this would take some time to develop. I suspect some mathematician out there has already solved this problem, but I don't know his/her name.

I don't want to discourage you, but I do want you to know that what you are asking is not trivial, and you still must resolve the problem of the multiple solutions, which cannot be done in the absence of other information about the disbursements. If, however, you have other information that can be used, such as how much the balance changed on certain dates, or you know for sure that certain disbursements are included in the total, the problem could become tractible.

I hope I have produced more light than heat here.

Damon

Thanks for your response, Damon. I wasn't really as concerned about paragraph #1 (ie.. having multiple combinations also add up to the number in question) as I was about paragraph #2. I figured it would have to be some intense macro that would exhaustively search thru each possible combination. Even using other criteria to narrow the numbers down such as vendor number, wouldn't help that much. Believe it or not, I've gotten pretty good just by eyeballing close to 2000 numbers each month. Sad- but that's all I've got to work with for now. Hopefully there is some mathematician that has solved this problem. Anyway, thanks for your response.

The data is organized by:

Date
Reference number
User
Disbursement type:(check, WireXfer, ACH)
Vendor number
Amount

I can narrow down my pool of selections by choosing only certain disbursement types and also by selecting only certain "Users". However, the complicated part is that the groupings would always be changing. For example- I may be looking for an amount that I know "Joe" entered as a check payment and the next amount I would be looking for is an amount that "Jane" entered as a wire payment. And for some payments, such as "ACH", it could include entries by both "Joe" and "Jane". The subsets would always be changing. That's why I was hoping for some superduper program that could just do a great sweep of a whole record of numbers (when I say "records", I mean numbers (or amounts) that also have corresponding fields that make them unique so that I know for sure that it fits into the solution.) I'm sorry if this is confusing, but it's a pretty strange reconciliation.

Hi again Lori,

The fact that you are able to solve this problem by "eyeballing" it suggests that there is some extra information you use that, when applied, reduces the number of combinations that have to be looked at to very manageable levels. Based on this, I would say that an algorithm that could do this should not only be possible, but also be not very difficult. In addition, it should run very fast (after all, it should be able do whatever you do much faster than you can do it).

I really don't think that a mathematician will come to your aid with the problem stated as is. Without that extra information, the problem may be intractible even for computers.

Damon

Lori,

I have been reviewing this page attempting to learn VBA in Excel so I don't know how to do it in Excel, but I would possibly try one of the other languages that you may be familiar with.

Here is a little routine in QBasic that would find 3 numbers which could be expanded to more numbers. The input of your file would be all that you would need to do.

Just a thought.
---------------------------
REM Check for matching sums Should handle 3 numbers that sum up out of 1000 numbers
DIM amount(1000)
INPUT "Amount to Match"; AtoM
REM Input amounts from your file in amount(xyz)
FOR a = 1 TO 1000
FOR b = 1 TO 1000
FOR c = 1 TO 1000
test = amount(a) + amount(b) + amount(c)
IF test = AtoM THEN PRINT amount(a); " "; amount(b); " "; amount(c); " "; a; " "; b; " "; c; " "
REM above shows Amounts that add up as well as what line numbers they are
NEXT c
NEXT b
NEXT a