Number generator and combination

[Guest]

I have 16 numbers in the A group (1A - 16A) and 16 numbers in the B group (1B
- 16B).
Condition 1: Each number in A has to be combined with 6 unique numbers in
the B group in a random order.
Condition 2: Two numbers in A cannot meet with a B member at the same time
(e.g., 3A and 4A cannot be combined with 1B in round 1).

Is there a way for excel to randomly generate these combinations? Thanks in
advance.

 

[Bernie Deitrick]

Jess,

Try the macro below, with a blank sheet active.

HTH,
Bernie
MS Excel MVP


Option Explicit
Sub TryNow()
Dim iCol As Integer
Dim ReSortGroup As Boolean
Dim j As Integer
Range("A1").Value = "A Teams"
Range("A2:A17").Formula = "=ROW()-1&""A"""
Range("A1:G1").WrapText = True
Range("B1:G1").Formula = "=""B Teams"" & char(10) & ""Round "" & Column()-1"
Range("B2:B17").Formula = "=ROW()-1&""B"""
With Range("A1:G17")
.Value = .Value
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.HorizontalAlignment = xlCenter
End With
For iCol = 3 To 7
ReSortGroup = True
Range("B2:B17").Copy Cells(2, iCol).Resize(16)
Cells(2, iCol + 1).Resize(16).Formula = "=RAND()"
While ReSortGroup
ReSortGroup = False
Application.Calculate
Cells(2, iCol).Resize(16, 2).Sort key1:=Cells(1, iCol + 1)
For j = 1 To 16
If Application.WorksheetFunction.CountIf(Range(Cells(j + 1, 2), _
Cells(j + 1, iCol)), Cells(j + 1, iCol).Value) > 1 Then
ReSortGroup = True
End If
Next j
Wend
Next iCol
Cells(2, 8).Resize(16).Clear
End Sub