Function generate_sorted_combination(how_many As Byte, from_set As Byte) As Variant
Dim arr_of_random As Variant, result_arr As Variant, i As Byte, j As Byte, threshold As Double
ReDim arr_of_random(1 To from_set)
ReDim result_arr(1 To how_many)
Randomize Time
For i = 1 To from_set
arr_of_random(i) = Rnd
Next i
threshold = WorksheetFunction.Small(arr_of_random, how_many)
For i = 1 To from_set
If arr_of_random(i) <= threshold Then
j = j + 1
result_arr(j) = i
End If
Next i
generate_sorted_combination = result_arr
End Function
Function count_triplets(ByVal combination As Variant, a1, a2, a3) As Long
Dim i As Byte, counter As Byte
For i = 1 To UBound(combination)
counter = counter + IIf(combination(i) = a1 Or combination(i) = a2 Or combination(i) = a3, 1, 0)
Next i
count_triplets = counter
End Function
Sub test()
Dim arr As Variant, triplets As Variant, i As Integer, j As Long, k As Byte
triplets = Range("A1:C90").Value
Do
arr = generate_sorted_combination(15, 25)
'Debug.Print WorksheetFunction.CountIfs(arr, 2)
k = 0
For i = 1 To 90
k = WorksheetFunction.Max(k, count_triplets(arr, triplets(i, 1), triplets(i, 2), triplets(i, 3)))
If k > 2 Then Exit For
Next i
If k < 3 Then
j = Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).Row
Cells(j, "H").Resize(1, UBound(arr)) = arr
End If
If j >= 65 Then
ActiveSheet.Range("$H$5:$V$65").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7 _
, 8, 9, 10, 11, 12, 13, 14, 15), Header:=xlYes
j = Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).Row
End If
Loop Until j >= 65
End Sub
Bookmarks