Forum Discussion
Finding the best combination of values using solver
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.
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
PeterBartholomew1 Nice use of CHOOSE with a Spin Button to step through the formula. Fun indeed! š
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.
PeterBartholomew1 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. š
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
.
