Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    9
    Thanks
    0
    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?
    thanks
    andrew macdonald

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    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
    Randomize
    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
    Loop
    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)
    Else
    Worksheets("Sheet2").Range("A1").Offset(Int(I / 2), 1).Value = Worksheets("Sheet1").Range("A1").Offset(iUsed(I), 0)
    End If
    Next I
    End Sub
    </pre>

    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
  •