Random Pairs Macro

[franguev]

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!!!

[Jacc]

This solution works but I would be surprised if no one found a better way of doing it.

To test it I needed a list of random names. What do you know, everything is available on internet...
http://listofrandomnames.com/

[franguev]

Jacc, thanks for your reply! Didnīt know about that index formula!
The solution looks pretty good, but I'm afraid I canīt apply it in this case, as every member of the list needs to be audited, but also must audit someone. I think I did'n make this point clear in the first message, or didn't thought about it:
1st condition: "Name n" must be paired with "Name i" (ie "Thom" audits "John")
2nd condition: "Name j" (or "Name i") must be pared with "Name n" ("Mike" or "John" audit "Thom")
Splitting the list in two doesn't allow this last condition. In other words, a name must be in both columns that make a pair, but the name mustn't be in the same line.
Many thanks

[mehmetcik]

Hi Sorry I am missing something but.

Why not sort your list of names randomly. create a random number in a helper column and sort by that column.

Then paste your two sets of your list of names next to each other but offset by one row.

Move the last name to the top, opposite the first name.

Done.

[franguev]

It could work also, but it must be automated as I donīt know who is the person that is going to manage this pair generator spreadsheet yet (what if that person is not a friend of excel). I would rather do it as simple as possible, this means, create the name list, hit a button and voila, pairs are ready... I thought about a way of doing it, but I donīt know how to create a loop with a macro, what about creating a macro with this last procedure? (I donīt know how to do it either)

[mehmetcik]

Ok I will create the macro for you

If I put the two lists in columns A and B would that do for you?

I cannot see that any loops would be required for this.

It is a max of six lines of code.

[mehmetcik]

Ok

your code is as follows:-

Please Login or Register  to view this content.

A sample sheet is attached.

Enjoy.

[shg]

There's a workbook with two ways to do this at https://www.box.com/s/ug8etrfi404n3au5md5g

[franguev]

Great job! That will work just fine, thank you!!!

[Jacc]

Another solution