Finding amounts that sum to a number

[Nikki0195]

I have a LARGE spreadsheet of items that I am trying to reconcile for an
entire month range. The issue that I am having - the bank statement may show
one amount ($1,000), where the spreadsheet may have 4 random amounts that
equal $1,000. I need to be able to find the items that equal a specified
number without having to manually add up several amounts and hope for a
match. Any suggestions? I have tried Conditional Formatting, Conditional
Sum, and the Solver add-in. None of these will give me what I need -
considering that I have about 400 items in a month.

Thank you all so much for taking the time to read this!

 

[Tom Hutchins]

Try these instructions for Solver, from a reply by B. R. Ramachandran to a
similar question:

Supposing the set of numbers is in A2:A11. Enter 1 in each cell in B2 to
B11. Place the single number in C2. Enter the following formula in some
other cell (say C3)
=SUMPRODUCT(A2:A11,B2:B11)-C2 and click ENTER.

Now you are going to use the Solver (the "Solver" add-in should be installed
for this) to find the combination of numbers in Column A whose total would be
equal to the single number you have entered in C2. For that,
"Tools" --> "Solver" --> in "Solver Parameters" window,

"Set Target Cell" $C$3
"Equal To" "Value of" 0
"By Changing Cells" $B$2:$B$11
"Subject to the Constraints"--> click "Add" --> enter $B2$2:$B$11, select
"bin" from the popdown list (This would add a constraint which would read as
"$B$2:$B$11=binary")
Click "Solve"
The solver would find the solution by changing some of the 1's in Column B
to 0's. The set of Column A numbers for which Column B is 1 (and not 0) is
the solution for your problem. If the solution is satisfactory, click "Keep
Solver Solution". Note that if more than one solution is possible, Solver
would find the first solution.

To add a nice touch, maybe you can use Conditional Formatting to highlight
the numbers in Column A for which Column B is 1.

This only finds a single solution. I (Hutch) have a lengthy recursive macro
that can find multiple solutions (if they exist). If the Solver approach
doesn't satisfy, let me know and I can post my code for you.

Hope this helps,

Hutch

 

[Mike H]

Hi,

This is enormously difficult. I kept the link to a solution posted by Harlan
Grove which in my opinion FWIW is an awesome piece of coding. Note in the
comments at the top of the code you need to set the 2 references this is done
in VB editor using TOOLS|References.

http://www.microsoft.com/office/com...4a844f-b8e9-43c6-a4aa-cda1dea15664&sloc=en-us

Mike

 

[Jim Thomlinson]

Your data set is too large for this type of analysis. The number of possible
permiutations and combinations that will add up to a given number will be
staggering and the processing time will be measured in months...

For example in this list of 30 numbers.
85.89
53.27
2.97
7.00
80.70
33.91
60.62
10.28
44.61
42.28
6.74
96.54
93.79
29.29
49.43
39.03
63.34
22.82
69.89
49.47
76.24
52.15
40.29
55.69
58.80
22.52
91.93
20.39
34.65
45.26


There are 294 different combinations that add up to 275.35. With a list of
400 numbers the possibilites are massive.

If nikki manages to do some of the matching to cut the list size down then
it might be possible, but there will still be a very substantial amount of
manual labour required. Generally speaking your search list needs to be less
than 50 before the analysis becomes feasable.