Given a list of n names, I would like to create random pairs, with no repetition (no pairs with the same person), just like a secret santa gift selection, or a round robin tournament with only one round. The idea is to create a spreadsheet to program audits between personnel of a company. So "Name n" should audit "Name i", and there's no problem if also that same "Name i" audits that same "Name n", but no "Name n" could audit himself. First attempt was to list the names in a column, create a random number in the next column, then rank the random column, and match the ranked number to a name using vlookup to create the pair. This is an example for 5 names... but it should work for any number of given names.
# name random ranking vloockup
1 name1 0,79178235 3 name3
2 name2 0,399734465 5 name5
3 name3 0,81276639 1 name1
4 name4 0,803179442 2 name2
5 name5 0,583473851 4 name4
It works pretty good, but every time the ranked number is the same than the list number (#), the pair results in the same person (the same thing happens if random numbers are repeated). So I made an other column to spot this error (comparing both names of the pair), and recalculate the spreadsheet until the error disappears, using f9 key every time (which would create new random numbers).
# name random ranking vloockup REPEAT!
1 name1 0,443535597 2 name2
2 name2 0,891630138 1 name1
3 name3 0,216194185 5 name5
4 name4 0,24676049 4 name4 ERROR
5 name5 0,301665665 3 name3
The longer the list, the faster I get to a good result (less chances for a ranked random number to coincide with the list number), but still want to create a spreadsheet that works without manual iterations.
I'm not very familiar with macros, but is there a way to create a macro that reads the value of one specific cell, say the header of the error column which could count in the column if there's any repetition in any pair and give a status (OK, REPEAT), and then recalculate the spreadsheet, until errors disappear? Then by clicking a button with this macro, random numbers should be calculated and recalculated until pairs are matched without repetition, end of the problem! Wish it was that easy!!!
(Or may be there's better way to aboard this problem)
Thanks!!!
Bookmarks