I have a group of people that have each submitted presentations for a conference. Each presentation needs to be reviewed 7 times by the other submitter's but no submitter should review their own presentation. I would like to randomly assign each person to review 7 presentations, with each presentation only being reviewed 7 times and no person reviewing their own.
DT = data.frame(First_Name = letters[1:10], Presentation = 1:10)
Place the members of the group in column A:
and run this macro:
Sub Reviewers() Dim N As Long, i As Long, j As Long, rA As Range N = Cells(Rows.Count, "A").End(xlUp).Row Set rA = Range("A1:A" & N) ' '----------------------------------PART 1 ' For i = 1 To N j = i + 1 rA.Copy Cells(1, j) Cells(i, j).Delete shift:=xlUp Next i ' '---------------------------------PART 2 ' For i = 2 To N + 1 Call SkrambleRange(Range(Cells(1, i), Cells(N - 1, i))) Next i End Sub Sub SkrambleRange(rng As Range) Dim arr(), r As Range, i As Long ReDim arr(1 To rng.Count) i = 1 For Each r In rng arr(i) = r.Value i = i + 1 Next r Call Shuffle(arr) i = 1 For Each r In rng r.Value = arr(i) i = i + 1 Next r End Sub Public Sub Shuffle(InOut() As Variant) Dim i As Long, j As Long Dim tempF As Double, Temp As Variant Hi = UBound(InOut) Low = LBound(InOut) ReDim Helper(Low To Hi) As Double Randomize For i = Low To Hi Helper(i) = Rnd Next i j = (Hi - Low + 1) \ 2 Do While j > 0 For i = Low To Hi - j If Helper(i) > Helper(i + j) Then tempF = Helper(i) Helper(i) = Helper(i + j) Helper(i + j) = tempF Temp = InOut(i) InOut(i) = InOut(i + j) InOut(i + j) = Temp End If Next i For i = Hi - j To Low Step -1 If Helper(i) > Helper(i + j) Then tempF = Helper(i) Helper(i) = Helper(i + j) Helper(i + j) = tempF Temp = InOut(i) InOut(i) = InOut(i + j) InOut(i + j) = Temp End If Next i j = j \ 2 Loop End Sub
Part 1 produces a list of reviewers for each submitter. So column B is the reviewer list for Mary Smith (Mary Smith's own name has been removed); column C is the reviewer list for Patricia Johnson, etc.
Part 2 shuffles each of the reviewer columns.:
To get the 7 reviewers for Mary Smith, pull the first 7 names from column B.
To get the 7 reviewers for Patricia Johnson, pull the first 7 names from column C, etc.