Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Pairings with a Caveat
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Pairings with a Caveat
"Anthony" wrote:
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),5 0)+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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Random pairings in Excel? | Excel Discussion (Misc queries) | |||
Remove Duplicate with 'Caveat' | Excel Worksheet Functions | |||
Race Pairings | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |