josh453 - 1 year ago 84

R Question

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.

Example Data:

`DT = data.frame(First_Name = letters[1:10], Presentation = 1:10)`

I am open to doing this in either R or Excel. Any help is appreciated.

Answer Source

Using *Excel*:

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.