# Thread: pairing up (excel 97&2000)

1. ## 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. ## 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>

#### Posting Permissions

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