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