josh453 josh453 - 1 month ago 19
R Question

Randomly Assign Data

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

Using Excel:

Place the members of the group in column A:

enter image description here

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.:

enter image description here

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.

Comments