What are some of the specific criteria for Formula One?

Counting all combinations of a list of entries up to a set of multiple criteria

• QUESTION: OK, I've looked at dozens and dozens of forums on the net. Not much specifically to this one. Bob I would love it if you could solve this one. I use many different parts that are ordered in standard bulk lengths but are then cut down for customer's specific needs. In order to calculate the parts most efficiently to reduce waste and manage inventory, I will have a user to input any number of entries of the cut lengths (values) needed for the job, into an array (spanning 30 cells or so, 10 columns, and 3 rows). 1. Since the user might input anywhere from 1 to 30 different cut lengths (values), the occurrences of cut lengths to count from will be relative. 2. Once entered, run all combinations of entered cut lengths (values) to pick and match the ones that will add up to the specific bulk part that it can find. Yes it might need to use multiple parts. For example: the user enters their requested cut length sizes (values) such as: 4, 12, 23, 74, 23, 45, 61, 58, 74, 59, 36... The formula calculates all combinations to determine 1. Which combination of these 11 cut lengths (values) will sum up to use standard bulk length(s) from the available 25, 50, or 75 inch sizes then, 2. How many of each standard bulk length will need to be used in the job. 3. I think the hardest of all is that it cannot count the same cell twice, even while the same cut length (value) could be entered more than once throughout the list. I would prefer a solution in a formula, because I'm not accustomed to VBA. But whatever gets the job done. Thanks Bob ANSWER: There is no possible formula to do what you want since one formula can't supply the multiple answers you're searching for. You're going to need to either use Solver or VBA, and I think this lends itself better to solver, but that will give only ONE solution, yet it's the easier of the 2 solutions, both of which are trial & error. Ensure you have access to the Solver addin. Assuming your list of values are in A1:A11, for example (given your sample list), enter this in C1 and fill down to C11: =A1*B1 (yes, B1:B11 is blank). In C12 enter =SUM(C1:C11) Call up solver, use Set C12 to 75 (or 50 or whatever # you want). Add 2 constraints: B1:B11>=0, B1:B11 are Integers. Then click Solve. After it churns for awhile, you'll get your solution. For example, for 75, Solver found 4 4's and 1 59 (totals 75). HTH, sorry it took so long to get back to you. ---------- FOLLOW-UP ---------- QUESTION: Thank you Bob. No problem for the length of time to reply. Solver seemed like a great way to customize this to my own needs. I tested your solution in a blank sheet and had some limited success. I'm confused as to why column B would be left blank and how in the world it's adding into the B column (range) when I'm setting to solve C12? It also seemed that in order to solve answers for additional criteria (for #50 or #25), any other cell I selected and wished to set brought up the same Solver parameters as my first c12 when solving for #75. Regardless, it was even less successful when attempting to solve in my input range on my real sheet and entering the constraints exactly as you instructed proved horribly impossible.would not let me get passed errors when changing the integer constraint after my first attempt. Then again, even if it could solve as I wish, is seems that my user would need to initiate solver each time. I am looking for another solution since my spreadsheet needs to calculate instantaneously for the user and I have a whole lot more going on in my sheet. Can you show me the same first scenario done with VBA?

• Answer:

  I may have left out that you need to set B1:B11 as the changing cells (sorry). However, even if you use VBA it's still going to be a long iterative process, similar to what Solver is doing. To create such a macro is way too time-consuming for me especially given that if I do it right, I'd wind up mimicking Solver anyway! There is no instant formula-based answer, I'm afraid.