find sum in list of of numbers

[Jim Thomlinson]

My appologies for not documenting where I had made modifications to your
code... As a professional courtesy I should have done that and I will
endevour to make the necessary notations at my end. Thanks for sharing your
work and once again I appoligize.

As for long variable names I have always favoured them purely from a
readability standpoint. I have debugged too much code written by others that
was almost impossible to follow. Not to mention it keeps things straight in
my head when I am writing it. Probably more the latter than the former...

 

[Dana DeLouis]

Hi Harlan. I love your "FindSum" program. It's excellent! Just for
feedback, in a permutation timing program that I have, I was coming up 1
number larger in the total number of solutions. Tracing the program back,
it appears to me that if the list is sorted, then the program misses the sum
of the first 'n' items. For example, if the op's data were sorted, then it
would miss finding the sum of the first two items (207.70+240.16 = 447.86)

A more simplier test might be with the number sequence 1,2,3...10. A
search for 3 might miss 1+2, or a search of 6 might miss 1+2+3.
Again, only if the data is sorted. I'm not sure at this point where in the
program to make a recommendation. Excellent code though. :>)

 

[Ron Coderre]

Just for the record, Harlan, I wholeheartedly agree with everything you et
al have mentioned regarding cross-matching details to totals. These
scenarios rarely have an elegant solution....having been there and done that
during the 12 years I spent as a financial/accounting manager. I never once
considered Solver more than a shot-in-the-dark approach to try first, just
in case. The basic problem is huge! After all, it took someone with your
intellectual horsepower to finally come up with code that has a reasonable
shot at dealing with the issue.

Unfortunately, the best defense is to try to do everything feasible to avoid
the situation and hope the worst case never happens.

Regards,
Ron

 

[dvpetta]

Wow, thank you so very much for all the responses. I've gone blind
looking at these amounts over the past few days so I really appreciate
the responses. First off, amongst some of my accountant co workers I
know alot about excel but the VBE stuff is a bit shady for me. I've
done some macros but the code Mr. Thomlinson provided is a bit out of
my league. I'd need a more thorough explanation as to how to set up
the code for me to make that work. I pasted it into VBE and turned on
those options but I don't know what to do thereafter. I also want to
try out the solver option but I need to have it installed. I'll have
to try it at work tomorrow morning, man I really didn't expect all
these responses this quickly, guess it helps posting in more than one
group.

For the record it seems that a few of the invoice amounts were missing
from my list hence the huge problem with matching them up to payments.
Even with that i still have some matching issues. I also forgot to
mention that an invoice amount once used when matched to a payment
cannot be used again. The combinations of course must match up to the
payments to the penny.

Again I really would like to try the VBE FindSum program but I'll need
some newbie introduction into how to write a code like that and then
how to use it. Thanks again!

Dza the accountant

 

[Niek Otten]

Look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[BrianP]

If your list only has up to 9 values, the following worksheet functions
will do the trick.

1. In range A1:A9 enter the list of amounts

2. Enter you're the figure you need to reconcile in A11

3. In the range of cells A15:I525,
enter the formula "=VALUE(MID(DEC2BIN(ROW()-14,9),COLUMN(),1))"

4. In the range J15:J525,
enter the array formula "{=MMULT(A15:I525,A1:A9)}"

5. In the range B1:B9,
enter the formula "=OFFSET($A$14,MATCH($A$11,$J$15:$J$525,0),ROW()-1)"

Amounts that reconcile to the figure in A11 will have a 1 next to them
in column B