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.
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. 🙂
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.