Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Thanked 0 Times in 0 Posts

    pairing up (excel 97&2000)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    a column contains a list of names of people, one person to a cell.
    What I would like excel to do is randomly select from this list of people and pair them up randomly. So say 20 people in list then have 10 pairs after calculation.
    Yes I could put all the names in a hat and pair up that way but hey why have excel and not use it.....
    Is there a formula that could let me do this?
    andrew macdonald

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: pairing up (excel 97&2000)

    The following code will take the list of names in column A on Sheet1 and pair them randomly in columns A & B on Sheet2:

    <pre>Public Sub Pair()
    Dim iMax As Integer, iUsed(500) As Integer
    Dim I As Integer, J As Integer, iRand As Integer
    iMax = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    I = 0
    Do While I <= iMax
    iRand = Int((iMax + 1) * Rnd)
    For J = 0 To iMax
    If I = J Then
    iUsed(I) = iRand
    I = I + 1
    Exit For
    End If
    If iRand = iUsed(J) Then
    Exit For
    End If
    Next J
    For I = 0 To iMax
    If I Mod 2 = 0 Then
    Worksheets("Sheet2").Range("A1").Offset(Int(I / 2), 0).Value = Worksheets("Sheet1").Range("A1").Offset(iUsed(I), 0)
    Worksheets("Sheet2").Range("A1").Offset(Int(I / 2), 1).Value = Worksheets("Sheet1").Range("A1").Offset(iUsed(I), 0)
    End If
    Next I
    End Sub

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts