Pairing people according to strengths

I've not heard of such a program (it does not mean it does'nt exist ;-) ), but you could probably make a kind of "difference table" with presonX vs presonY comparison (so n by n table, where n is number of participants.
And my proposition for "measuring difference" is sum of squared differences in each category. Squared to: a) do not think stronger/weaker - just different b) to emphasize bigger differences.
and then try to select pairs that have lowest values of this difference measure.

For small number of participants the program could check all possible divisions but then with growning number of participants probablu some optimization (and may be leading just to good selection, not necesserily the best one) routtines would be needed.

No its not difficult
We just need a format to work upon your requirements
So just upload a sample format with few examples so that we have a better understanding...
To upload a workbook Click on GO ADVANCED and use the paperclip icon to open the upload window.

http://www.excelforum.com/members/davesexcel-albums-excel-pictures-picture149278-attach-a-file.html"]View Pic[/URL]

Ohk I am trying to accomplish it...

Hi,

See attached file.
I did it basically step by step (or "helper column by helper column" ;-) ) so shall be reasonably easy to understand.
Yellow cells have formulas (which were copied down). Gray is a "handwritten" 1. Note that both U2 and U3 have (diffrent) formulas inside.

It is a bit easier to modify to different number of participants when they are listed opposite way than you did. (transposed)

See also sheet2 - with similar playes mathed (this was my initial thought, that you want to math them by similarities, not by dissimilarities.

And Sheet 3 with 32 players ! (the skills of next 24 are randomly assigned here :-)) - note that some (depending on result of particular random run) are even "better" than your Alan and Ben :-)

A different approach, using VBA:

Figure out a metric that that measures the fairness of the entire set of teams. (Not sure what that would be, but something like minimizing variance.)

Assign pairings at random (e.g., in alphabetical order).

Create a loop that tries every possible swap, and reevaluate the metric after each one. If the swap improves the metric, keep it. Loop until no swap offers improvement.

General comment: I would allow duplicates, so someone could, for example, rank science, biology, & literature as #1, history & geography as 4, sports as 6, and music, film, & art as 7. My knowledge of music and art are negligably different and close to zero.

As for #ref: yes, to keep formulas as simple as possible, I hardcoded the range addresses so if you plan to have other number of participants than 8 or 32 (you have already prepared)
you have two (well: three) main options:

First one: write all participants and their skills (or if you do not know them - include dummy names and dummy skills) and go one by one from left-up going down-right through yellow cells and update formulas to follow your data range.
After changing each yellow cell (if it have ancessors below) - copy it down (paste formulas only) checking how much down you have to go.

The second option would be to create dynamic range names (in name manager: Ctrl+F3) but it would be a it more fancy solution.

The third would be (as you posted your question on VBA subforum) - if you are satsfied with the algorithm we tested here - rewrite it from formulas version to VBA one :-).

I proposed simple formulas (unfortunately it leads to more work during sustomization), because you declared to be "novice in Excel".

Change also othe addresses.
Columns M and N (and as a consequence O-U too) shall be extended down from current row 497 to 564 (yes it's growning quickly) so all addresses with $497 shall be also updated

Thinking of possibilities of some brute-force approach, I did some small additional research.
Well with 32 persons, one shall give up brute-force ideas and rely either on such simple algorithm as above:
find a most different pair, and then go down the ranking of diffreneces skipping these pairs, where one of members is already selected,
or go some "fuzzy logic or random mixing algorithms.

But anyway you simply cannot use brute force, do a list of all possible pairs:
AB CD EF GH IJ KL ...
AC BD EF GH IJ KL ...
etc.
and calculate for each of this set for instance sum of "fitting quality" measure for all pairs.

Why? Because the list will be 31!! (double factorial !) long.

It is more than 10^17

If you had whole year to do calculation, your machine would have to check 6 085 070 521 each second, yes 6G+ compared per second. Really something.
Not to mention storage space requirements (it basically is not necessary, so speed is main limit)
If someone would have a concept to store each of compared list of pairs. 10^17 items 10 000 Terraitems :shock:

So I'm giving up the idea to use bruteforce on this.