I have a column of numbers (lets say it's A1:A100) and I need to find a subset of them that sum to a certain total.
It's possible with the Solver add-in*. The following steps worked for me in Excel 2007 and 2010.
If the solver is taking a long time, you can help it out by removing rows that obviously won't work (total is in dollars, and only one row has nonzero cents)
Bonus: You can have excel automatically highlight the cells that you're looking for by adding conditional formatting to those cells. Select all of the cells you want to format and from (Home tab)>>(Styles group)>>Conditional formatting>>New Rule select 'Use a formula to determine which cells to format'. In the formula, enter '=$B1=1' (no quotes) which will evaluate to true if the corresponding row in the B column is 1. For the format, you can add whatever you want (bold, italic, green fill, etc).
Another easy way to find the important rows is to sort column B Z->A, and all the 1's will come to the top.
*The solver add-in can be installed with these steps
There is a low cost Excel Add-in SumMatch, which will highlight the subset of numbers that add up to a target sum.
|show 3 more comments|