Random Pairings with a Caveat

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

I would like to pair 50 agents into teams of two. This will be done
each week for seven weeks for a Cup Series incentive at work. I found
the thread that shows how to create random pairings but can I prevent
the same people from being paired up more than once during the
incentive?
 
Anthony said:
I would like to pair 50 agents into teams of two.
This will be done each week for seven weeks for a
Cup Series incentive at work. I found the thread
that shows how to create random pairings but can
I prevent the same people from being paired up more
than once during the incentive?
Click to expand...

Yes. I suspect this can be simplified, but here is one approach.

Suppose the names are in A2:A51. (Reserving row 1 for titles.)

With the method below, I feel there is no need to randomize the names; they
will appear to be sufficiently random, IMHO. But if you wish, you can fill
B2:B51 with =RAND(), then select A2:B51 and sort column B. Then you can
delete B2:B51.

Then we generate the 25 teams for each of 7 weeks.

I think this is easiest to do using a VBA macro. See the macro below.

But if you are not comfortable with VBA, you could set up the following in
Excel.

In C2:C8, enter the numbers 3, 7, 11, 13, 17, 19 and 23. (Prime numbers,
explained below.)

In E2:E26, enter the numbers 0 through 24. (Team numbers.)

Enter the numbers 1 through 7 into F1, I1, L1, O1, R1, U1 and X1. That is,
every 3rd cell starting with F1. (Week numbers.)

Enter the following formulas:

F2: =INDEX($A$2:$A$51,MOD(2*$E2*INDEX($C$2:$C$8,F$1),50)+1)
G2: =INDEX($A$2:$A$51,MOD((2*$E2+1)*INDEX($C$2:$C$8,F$1),50)+1)

Copy F2:G2 into F3:G26 (24 rows).

Then copy F2:H26 (25 rows by 3 columns) into I2:Z26 (25 rows by 18 columns).

The pairs in F2:Y26 are the 25 teams for each of 7 weeks.

Explanation....

C2:C8 contains any 7 numbers that are relatively prime to themselves as well
as to the factors of the number of names. Since 50 = 5*5*2, I chose the
first 7 prime numbers other than 2 and 5. (I also excluded 1 to enhance the
random appearance.)

The formulas in F2 and G2 pair each p-th name with the next p-th name in
round-robin fashion.

The prime number "p" ensures that each pair in a week is unique.

Chosing a different prime number "p" for each week ensures that no pair is
repeated.

-----

Macro....

Option Explicit
Option Base 0

Sub gen25by7()
' *** CUSTOMIZE ***
Const src As String = "a2"
Const dst As String = "f2"
Dim i As Long, w As Long, r As Long
Dim p
Application.ScreenUpdating = False
Range(dst).Resize(25, 7 * 3).Clear
p = Array(3, 7, 11, 13, 17, 19, 23)
For w = 0 To 6
i = 0
For r = 0 To 24
Range(dst).Offset(r, 3 * w) = _
Range(src).Offset(i)
i = (i + p(w)) Mod 50
Range(dst).Offset(r, 3 * w + 1) = _
Range(src).Offset(i)
i = (i + p(w)) Mod 50
Next
Next
Application.ScreenUpdating = True
End Sub
 
Back
Top