Finding the best combination of values using solver

[Lost_inExcel]

Question: How do I use Excel built in solver to get the correct combination?

Objective: To purchase parts at lowest cost.

Constraints: Divvy up among 3 suppliers/out of 5 suppliers ; a total of 19 parts in some combination.

A solution could look like this:

Sup1-->	P1,P4,P7,P19,P16
Sup2-->	P2,P5,P8,P15,P13,P17,P18
Sup3-->	P3,P6,P9,P10,P11,P12,P14

Input data as follows:

Supplier	Part number	Cost
Sup1	P1	$10.00
Sup1	P2	$9.00
Sup1	P3	$5.00
Sup1	P4	$6.00
Sup1	P5	$6.00
Sup1	P6	$15.00
Sup1	P7	$11.00
Sup1	P8	$8.00
Sup1	P9	$71.00
Sup1	P10	$11.00
Sup1	P11	$12.00
Sup1	P12	$21.00
Sup1	P13	$22.00
Sup1	P14	$6.00
Sup1	P15	$31.00
Sup1	P16	$6.00
Sup1	P17	$4.00
Sup1	P18	$17.00
Sup1	P19	$8.00
Sup2	P1	$6.00
Sup2	P2	$8.00
Sup2	P3	$11.00
Sup2	P4	$4.00
Sup2	P5	$8.00
Sup2	P6	$34.00
Sup2	P7	$12.00
Sup2	P8	$8.00
Sup2	P9	$55.00
Sup2	P10	$77.00
Sup2	P11	$13.00
Sup2	P12	$25.00
Sup2	P13	$23.00
Sup2	P14	$2.00
Sup2	P15	$34.00
Sup2	P16	$12.00
Sup2	P17	$3.00
Sup2	P18	$13.00
Sup2	P19	$7.00
Sup3	P1	$9.00
Sup3	P2	$11.00
Sup3	P3	$4.00
Sup3	P4	$3.00
Sup3	P5	$2.00
Sup3	P6	$23.00
Sup3	P7	$13.00
Sup3	P8	$8.00
Sup3	P9	$45.00
Sup3	P10	$29.00
Sup3	P11	$14.00
Sup3	P12	$23.00
Sup3	P13	$24.00
Sup3	P14	$3.00
Sup3	P15	$49.00
Sup3	P16	$16.00
Sup3	P17	$1.00
Sup3	P18	$12.00
Sup3	P19	$6.00
Sup4	P1	$8.00
Sup4	P2	$13.00
Sup4	P3	$2.00
Sup4	P4	$7.00
Sup4	P5	$8.00
Sup4	P6	$55.00
Sup4	P7	$14.00
Sup4	P8	$8.00
Sup4	P9	$48.00
Sup4	P10	$34.00
Sup4	P11	$15.00
Sup4	P12	$22.00
Sup4	P13	$25.00
Sup4	P14	$8.00
Sup4	P15	$32.00
Sup4	P16	$13.00
Sup4	P17	$2.00
Sup4	P18	$11.00
Sup4	P19	$5.00
Sup5	P1	$9.00
Sup5	P2	$12.00
Sup5	P3	$1.00
Sup5	P4	$16.00
Sup5	P5	$1.00
Sup5	P6	$41.00
Sup5	P7	$15.00
Sup5	P8	$8.00
Sup5	P9	$26.00
Sup5	P10	$67.00
Sup5	P11	$16.00
Sup5	P12	$17.00
Sup5	P13	$26.00
Sup5	P14	$7.00
Sup5	P15	$22.00
Sup5	P16	$12.00
Sup5	P17	$5.00
Sup5	P18	$19.00
Sup5	P19	$4.00

[#2]

@Lost_inExcel 

To use Excel's Solver tool to find the best combination of values for purchasing parts at the lowest cost, follow these steps:

1. Set Up Your Worksheet:
• Enter the supplier names, part numbers, and costs into your Excel worksheet as shown in your input data.
2. Define Your Variables:
• Create a table to represent the quantities of each part purchased from each supplier. Each cell in the table will represent the quantity of a specific part purchased from a specific supplier. You can vary these quantities to minimize the total cost.
• Define the decision variables (quantities) in your Solver model. These will be the cells in your worksheet that represent the quantities of parts purchased.
3. Define the Objective Function:
• Determine the objective function that you want to minimize. In this case, you want to minimize the total cost of purchasing the parts. The total cost can be calculated by summing the product of each part's quantity and its corresponding cost.
• Create a formula in a cell to calculate the total cost based on the quantities of parts purchased and their costs.
4. Set Up Constraints:
• Define any constraints that need to be satisfied. In this case, you want to ensure that the total quantity of parts purchased from each supplier does not exceed the total available quantity of parts, and that each part quantity is non-negative.
• Create formulas or use Excel functions to set up these constraints in your worksheet.
5. Run Solver:
• Open the Solver tool in Excel. You can find it under the "Data" or "Analysis" tab, depending on your version of Excel.
• Set up Solver to minimize the objective function subject to the defined constraints.
• Specify the decision variables, objective function, and constraints in the Solver Parameters dialog box.
• Run Solver to find the optimal solution.
6. Review the Results:
• Once Solver has finished running, review the results to see the optimal quantities of parts to purchase from each supplier that minimize the total cost.
• Solver will adjust the quantities in the decision variable cells to achieve the optimal solution.

By following these steps and using Excel's Solver tool, you can find the best combination of values to minimize the cost of purchasing parts from multiple suppliers while satisfying any constraints you may have. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@Lost_inExcel I was able to solve this with Excel for MS365 using array functions and a Data Table. The first step was to generate a list of all possible combinations of 3 suppliers out of 5 using a custom LAMBDA function (PERMA). The second step was to create a MINIFS-based summary report to calculate the minimum cost for the first combination of suppliers using the Row_ID as a single input variable. The third step was to use a Data Table (Data > What-If Analysis > Data Table...) to generate the minimum costs for all combinations of suppliers (for every Row_ID variable) and identify the combination with the lowest total cost. Lastly, the results of the combination with the lowest total cost were filtered to generate the desired output.

Min Cost via Data Table

Please see the attached workbook to view all of the formulas used to generate the final results...

Additional resources:

• Finding unique combination of data (PERMA Function) 
• Calculate multiple results by using a data table 

[#4]

@djclements 

Thank you very much! Calculation is good.

Had question if :

I artificially put $1/- for each part for Sup1, the by default, it does not assign 19 parts to supplier1.

Why would that be the case

[#5 · Accepted solution]

@Lost_inExcel I'm not entirely sure, without seeing what went wrong. If you could save and attach the file (or provide a screenshot), I could possibly offer something more concrete. What version of Excel are you using? The formulas I used will only work with Excel for MS365. This is what it looks like when I input $1 for all parts for Sup1:

Min Cost via Data Table

As long as you input any Row_ID with the lowest cost (1 thru 6) in cell F13, it should assign all 19 parts to Sup1.

[#6]

Thank you, I confirmed your spreadsheet works perfectly in MSoffice365 laptop. So I must use Excel for MS365.

[#7]

@Lost_inExcel 

The attached works by exhaustive search.

=LET(
    distinct,       UNIQUE(part),
    supplierH,      TOROW(UNIQUE(supplier)),
    costArray,      MINIFS(cost, part, distinct, supplier, supplierH),
    combinations,   Combinationsλ(N, m),
    totalCosts,     BYROW(
        combinations,
        LAMBDA(combin, SUM(BYROW(FILTER(costArray, combin), MIN)))
    ),
    selected,       XLOOKUP(MIN(totalCosts), totalCosts, combinations),
    filtered,       FILTER(costArray, selected),
    bestCost,       BYROW(filtered, MIN),
    partBySupplier, IF(filtered = bestCost, distinct, ""),
    partList,       TOCOL(BYCOL(partBySupplier, LAMBDA(p, TEXTJOIN(", ", , p)))),
    return,         VSTACK(
        HSTACK("Best cost:", SUM(bestCost)),
        HSTACK(TOCOL(FILTER(supplierH, selected)), partList)
    ),
    CHOOSE(line, distinct, supplierH, costArray, combinations, totalCosts, selected, filtered, bestCost, partBySupplier, partList, return)
)

Just for fun, the workbook allows you to step through the formula line by line

[#8]

@Lost_inExcel 

Since the original question specified Solver, the correct combination of suppliers may be determined using an evolutionary search.

[#9]

@Peter Bartholomew Nice use of CHOOSE with a Spin Button to step through the formula. Fun indeed! :)

[#10]

@djclements 

Please see the excel error message 

[#11]

@djclements

sorry, your instruction is very clear.

input row ID in cell F13. 

Got it.

Thank you:folded_hands::folded_hands:

[#12]

@Lost_inExcel Glad you were able to figure it out. :) To help eliminate those potential errors/mistakes, I made a few small modifications to the file (see attached).

[#13]

@Peter Bartholomew 

When I changed the combination to 5C2, I get the following result, please help.

[#14]

It is very good. Thank you for the pointed arrow modification.
:folded_hands::thumbs_up:

[#15]

@Lost_inExcel 

That is odd.  This is what I get

It appears to be a problem with the Lambda function Combinationsλ

Combinationsλ
= LAMBDA(n, m,
    LET(
        binary, BASE(SEQUENCE(2 ^ n), 2, n),
        bits, --MID(binary, SEQUENCE(1, n), 1),
        criterion, BYROW(VALUE(bits), SUM) = m,
        FILTER(bits, criterion)
    )
);

It is a brute force approach that starts with any number of objects taken from N and only then filters for the m objects required.  Now as I look, I think the problem is that I have used an eta-reduced Lambda function and you are not using the insiders beta version of Excel.  Either switching to insiders channel or introducing an anonymous Lambda function to perform the summation by row should work

Combinationsλ
= LAMBDA(n, m,
    LET(
        binary,    BASE(SEQUENCE(2 ^ n), 2, n),
        bits,    --MID(binary, SEQUENCE(1, n), 1),
        criterion, BYROW(VALUE(bits), LAMBDA(x, SUM(x))) = m,
        FILTER(bits, criterion)
    )
);

Sorry about setting traps for you .  

[#16]

@djclements 

I have wondered about asking Microsoft for a function such as RETURN( ) or even IN( ) to close the local naming scope and return the contents of any variable by index (mod n) with zero as the default.

Looking at your solution, I realised that it represents the first time I have ever made a data table work (I messed up with selecting the range first time round so then had to reinstate the solution).  It will most likely be the first and last time because now, for better or worse, I am almost certain to define the calculation as a Lambda function and use MAP to generate the output list.

As a further point of interest, you may be interested in a workbook I posted in another discussion that uses SCAN and thunks to generate combinations.  The method is mind twistingly complicated but is reasonably efficient.  The calculation of 924 combinations corresponding to 12C6 took 3.5ms rather than 27ms required for a 'brute force approach.

[#17]

@Peter Bartholomew Thanks for sharing the combinations file. I took one look in Name Manager and thought "nope, not today", lol. It's impressive, to be sure, and highly efficient. With my limited knowledge at this stage, though, I wouldn't know how to convert the binary results into something usable (ie: supplier names or ID numbers). EDIT: I get it now... you would need to explode the binary results using something like =WRAPROWS(TOCOL(IFS(--MID(results, SEQUENCE(, n), 1), TOROW(suppliers)), 2), m).

I like my PERMA function for its simplicity and relative efficiency, although it's limited to 1,048,576 total permutations with repeats (and significantly less combinations without repeats) due to the row limitations of TOCOL. The most I can squeeze out of it is 10C6 (with a TEXTJOIN / TEXTBEFORE / TEXTAFTER variant) in approx. 11 seconds. Not nearly as robust as your method, but gets the job done with simple scenarios.

Truth be told, this was my first time ever using a Data Table. I didn't even know what they were for until about a month ago, when I saw Diarmuid Early use one in a recent YouTube video: Excel Origami - folding arrays with WRAPROWS / WRAPCOLS. When this question came along, it was the perfect opportunity to try one out for myself. Sure, a LAMBDA function could be written to output the same results, but the Data Table method was super easy to setup. The only obvious drawback I can see is that it's not dynamic (when adding another supplier, for example, from 5C3 to 6C3); however, it's a pretty simple process to clear the Data Table and re-create it. I can't say for sure if I would ever use one again or not, but all-in-all, it was a fun experiment. :)

[#18]

@djclements 

I can well understand why you deferred  any study of the combinations generator to such time as you might need it!  It took me a couple of days to sort out what I was doing.  I came to the conclusion that a decimal number in the range 0 to 2ᴺ-1 provided an efficient way of representing a subset taken from a set of N objects.  As you realised, the process for extracting the list of objects (suppliers in the present context) is to convert to a binary number, explode the digits, and filter the objects to return the subset.

To add a new object to each list, going from 5C2 to 6C3 (say), one merely needs to appends a 1 to each binary number.  Equally, to add to possibility of a further object without selecting it, 5C3 to 6C3, one appends a zero.  Working with the decimal representation, this is double and add 1 (or 0).  Pascal's triangle involves adding the total counts  6C3 = 5C2 + 5C3, but here I stacked the two lists to obtain the new combination.  The catch is, that once I had SCANned one diagonal, I needed all those values to initialise the enclosing REDUCE calculation on the other diagonal.  That is, an array of lists is essential to the calculation.  Hence the messing about with thunks.