Introducing TM Match Target
The TM Match Target add-in analyzes a list of numbers and finds combinations that sum to a given total. This has applications in a range of disciplines including processing receipts, reconciling payments such as health care insurance reimbursements or payments by a customer for many outstanding invoices, operations management and operations research, and supply change management.
There are a number of posts, easily found through Google or Bing, that address the same issue, including my own Find a set of amounts that match a target value. This add-in packages the analysis in an easy-to-use interface, includes a means to stop the analysis if it is taking too long and preserve the combinations found so far, together with various options on what results should be shown.
For more please visit http://www.tushar-mehta.com/excel/software/match_target/.
Hey cool. I’ve needed something like this a few times over the years. I usually do something funky using Solver, but would prefer something more exhaustive. Does your add-in try every combination?
For a formula approach for finding the closest combination of values that sum to a target dollar value, i think you could also try this method in Excel 2010 for up to 20 values…
Define names for Target value T, amounts A, match index n, for example: T=A1, A=A2:A10, n={1,2,3,4,5}.
Then define the named formula for values to include in sum V, given by the formula below:
and Ctrl+shift+enter {=V} in adjacent cells, for example in B2:F10 to return the closest five combinations of values.
Good. Could be very useful.
But be aware:
this is NOT free.
(should be pointed out in your post, IMHO).
Rob: Checking every combination is not necessary. The algorithm skips those combinations it knows cannot add up to the target sum. That requires (1) the data to be positive or (2) the negative numbers to be at the start of the list.
It is possible to reduce even further the number of combinations searched by organizing the positive numbers in descending order.