Forum Discussion
Solving 'The Assignment Problem' with Lambda
In attached file PQ Assignment2 generates the required rows only
Though, it's slower than djclements initial approach (PQ Assignment1)
In attached file PQ Assignment2 generates the required rows only
Though, it's slower than djclements initial approach (PQ Assignment1)
It may be a bit slower but it's easy to follow to step through it. This is quite clever!
DAX was not even on my list of potential solutions. This is impressive that it can be done but also the fact it's reads very easy.
Yesterday I believed there was room for another Lambda solution that could handle more than 3 tasks - maybe up to 10 or so. My thinking was to add a recursive element to the REDUCE portion of the formula:
Unrealized formula:
In seeing the various solutions being added it doesn't seem worth the effort. Also, Python can make quick work of the 'heavy lifting' portion of things in generating the permutations:
import itertools
list(itertools.permutations([1, 2, 3, 4], 3))
Patrick2788 OK so I am finally able to submit my solution. I didn't research the problem and other solutions so probably re-invented the wheel but:
I believe a valid solution must be some sequence of min for a given column followed by the min of another column until you reach all the columns (note removing the selected row each step). So given 3 columns if there were no duplicate min values that would be only 6 options.
So given that I have created 3 lambdas in the attached:
MinLocations : this is the 'master' lambda that will output the result
MinLocations_calcs: this will iteratively go through each possible column order finding all possible min solutions
DropLines: this is a useful utility to drop any specific rows or column from within a table/range
I have no idea of performance but submit that to others to find out 🙂
- This might be the most original formula solution posted because it looks like you went about generating the permutations a different way. You'll forgive me If I didn't respond sooner (I first opened the workbook after dinner the other day and knew this was one of the solutions which needed some time to study!). If I understand the solution correctly, MinLocations is doing most of the heavy lifting in identifying min rows for possible solutions. DropLines discards what's not needed. If I understand it correctly, even though there's a function to discard, not a lot of 'waste' is being generated with MinLocations. This is very different than a combination-based solution that generates tons of permutations and filters for what's needed. This solution may do well in a speed test.
- So this solution is based on the premise that the optimum solution must be the min val of some column followed by the min val of another column (excluding the previously used row) and so on until all columns are used. The challenge is know what order to look at the columns in. So it could look at as few as 6 options for 3 columns but if every single value is the same (per column) then it will blow up to every combination x 6. That said if we assume in most cases there aren't that many repeated values per column then it could have advantages but we have also seen that excel can be very very efficient and quick in created large matrices of values if/when the matrix is well defined. So it might be that this has less options to check but more manipulation and be slower for that reason. Maybe someone could also take this concept and do a more efficient way of implementing it. Maybe remove some of the recursion since that tends to slow it down. either way, looking forward to hearing how it does :). I also like my little DropLines function. I also created an InsertLines but felt that was less efficient than just doing it directly, which is prob true of the DropLines too.
BTW I think you meant to type MinLocations_Calcs for where the meat of the work is being done. MinLocations is just the wrapper that formats the data in, finds the best out, and formats accordingly.
