yogi_Find Mutually Exclusive Combination Of Values In Cells A3 to A11 and B3 to B11 That Yields Minimum Average Of Values In C3 to C11

                          Google Spreadsheet   Post  #1723
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-10-2014
post by Chris Schwarz:
(https://productforums.google.com/forum/#!mydiscussions/docs/BL06Yxlu5mg)
Optimization Problem: Mutually Exclusive Inputs and a Minimized Output

First, a little background:

Given two categories of inputs, call them A's and B's, a result, call it "C", is computed for all permutations of A and B.

For example, we have A1, A2, B1, and B2.  This results in A1-B1, A1-B2, A2-B1 and A2-B2.  Each combination may be considered a cartesian coordinate system (XXX|YYY - XXX|YYY), and the distance between them is the value of C.

What I would like to do is have an automatic minimization for the summed distance such that all A's and B's are used once and only once.

I hope this is clear enough of a description, but if not, I'll provide more details on the question below.

Given the following A's:

379|467

387|474

378|471

And the Following B's

470|505

472|505

447|506

We have the following permutations, with calculated value C

387|474	447|506	68.0
378|471	447|506	77.4
379|467	447|506	78.4
387|474	470|505	88.6
387|474	472|505	90.5
378|471	470|505	98.1
379|467	470|505	98.6
378|471	472|505	100.0
379|467	472|505	100.5

What I would like is for a function to take these results and give those combinations of A and B that produce the minimized value of all summed C's such that each A is only used once and each B is only used once.

This is necessary due to the ever increasing number of permutations with an increasing input - it makes doing it manually too time consuming and prone to error if done quickly.  For example, 30 A's and 30 B's yields 900 combinations whereas 31 A's and 31 B's yields 961 combinations.

Thank you in advane.

-----------------------------------------------------------------------------------------------------------------------------------------------