Finding the best combination of values using solver

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

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.

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 

Lost_inExcel 

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