+ Reply to Thread
Results 1 to 11 of 11

generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    2,179

    generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers from each of the trios in the list,
    example=if in the formation of 15 numbers you have a complete trio in the list you cannot. the macro has delete, the limit is for each triplet is zero to two numbers
    the number of lines generated can be 60 but can be more or less
    https://www.excelforum.com/attachmen...1&d=1666289023
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    show at least two results - at least one which is OK and at least one which is not accepted (and comment why it cannot be accepted)

    I think
    1 2 10 11 12 13 14 15 16 17 18 19 20 21 22
    fits your requirements
    but may be I am wrong
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    Quote Originally Posted by Kaper View Post
    show at least two results - at least one which is OK and at least one which is not accepted (and comment why it cannot be accepted)
    in a workbook

  4. #4
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    2,179

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    hello kaper thanks for seeing the post
    one draw= 01 02 03 04 05 10 11
    12 14 15 17 18 19 20 22
    this draw is what will show that there is not even a complete trio from the list of the 90 trio this is the one you want to play, kaper in your example has a long sequence, the sequence must have a maximum of 6,7,8 numbers, ex= 01 02 03 04 05 06 , because we will hardly have an uninterrupted sequence of more than ten numbers in a row , maximum up to 8 numbers in a row
    already in these other draw=02 05 06 07 08 10 12 13 15 17 19 21 23 24 25= in this draw we have 32 complete trios, this type doesn't work because there are trios in the draw with three numbers each trio, you can't have it because you tend to have to zero to two numbers, the generated line having a complete trio no longer works,
    kaper you can generate up to 60 more lines or whatever is possible, complying with the criteria of not having a complete uncle on the trio list

  5. #5
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    2,179

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers


  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    The attachment from post #5 is not a valid file :-(

  7. #7
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    2,179

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    kaper, objective, is to generate lines of 15 numbers from 01 to 25, but not accept complete triplets. trio with 3 numbers is not accepted, the filter is minimum zero maximum 2 of each trio, this type of draw is happening every 5th to 6 draws, when in the draw there are no complete trios

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    The code below would do.
    It generates random combination of 15 out of 25. Then checks if any of 90 triplets is included as a whole. If not, it writes it to the workbook.
    Then (as the combinations are randomly generated, there could be repeating combinations), duplicates are removed.
    The macro stops after collecting 60 unique combinations of 15 numbers, none of them including any of the triplets.


    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
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    2,179

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    ok, kaper work of art to be able to do in excel. very good work, this pattern of not having even a trio is in every 5th to 6 draws, this happens, being able to do these filters in excel is very limited, but who knows how to always do it.
    thanks for the hard work

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    If you just want to generate and then filter, you may use following code (recycled from one of my previous posts in excelforum.pl):


    Sub generuj()
    Dim ile As Long, poile As Integer, zilu As Integer, i As Long, j As Long, k As Integer
    Dim intriplet As Long, liczba As Long, wyniki() As String, triplets As Variant, localmax As Long
    ile = 60 'InputBox("ile losowań (max kilka tysięcy)")
    poile = 15 ' InputBox("Po ile liczb")
    zilu = 25 ' InputBox("Z ilu (49, 35 itp.)")
    triplets = Range("A1:C90").Value
    For i = 1 To ile
      ReDim Preserve wyniki(1 To ile)
      With CreateObject("System.Collections.ArrayList")
        Do
          liczba = WorksheetFunction.RandBetween(1, zilu)
          If Not .contains(liczba) Then .Add liczba
        Loop Until .Count = poile
       .Sort
       localmax = 0
       For j = 1 To 90
         intriplet = 0
         For k = 1 To 3
           intriplet = intriplet + IIf(.contains(CLng(triplets(j, k))), 1, 0)
         Next k
         localmax = WorksheetFunction.Max(localmax, intriplet)
       Next j
       wyniki(i) = Join(.toarray, ";") & ";" & localmax
      End With
    Next i
    Range("H6:W65").ClearContents
    Range("H6").Resize(ile) = Application.Transpose(wyniki)
    Range("H6:H65").TextToColumns Destination:=Range("H6"), DataType:=xlDelimited, Semicolon:=True
    End Sub
    I think your "every 5th or 6th draw" is too optimistic. In the attached file, there is the case (it happened to me at 4th run of the macro) where all 60 generated combinations had a triplet from 90 sample triplets. Of course it is not always like that.
    Attached Files Attached Files

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: generate 60 formations, but filtered from a minimum of zero and a maximum of 2 numbers

    The code was reused, so it contains some sub-optimal solutions.

    instead of
    For i = 1 To ile
      ReDim Preserve wyniki(1 To ile)
    it would be better to reverse the sequence, and don't use Preserve so:

    ReDim wyniki(1 To ile)
    For i = 1 To ile

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] generate formations of 15 random numbers, but must not have 14.15 points
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-30-2022, 06:38 AM
  2. [SOLVED] generate formations of 15 numbers from 01 to 25.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-02-2020, 12:46 AM
  3. Replies: 6
    Last Post: 04-29-2019, 08:32 AM
  4. please, generate all possible formations using zero and the maximum 3 digits.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2019, 01:36 PM
  5. hello please generate formations of 5 numbers in the criteria *.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2018, 11:15 AM
  6. [SOLVED] Request = generate the maximum formations 3 letters of the 10 letters
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2017, 09:20 PM
  7. Maximum & Minimum values - filtered columns across sheets
    By naik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2011, 12:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1