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