Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number generator and combination
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number generator and combination
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 "Jessshin" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NUMBER SEQUENCE GENERATOR | New Users to Excel | |||
same number appears in a random number generator | Excel Worksheet Functions | |||
Random Number generator | Excel Worksheet Functions | |||
Random Number Generator | Excel Worksheet Functions | |||
Random number generator | Excel Worksheet Functions |