Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 26 of 26

Thread: find all combinations (no repeats)

  1. #21
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,912
    Try the likes of
    Sub Blah()
    Set Source = Range("A1:A15")
    Set Destn = Range("B1")
    For g = 1 To 12
      For h = g + 1 To 13
        For i = h + 1 To 14
          For j = i + 1 To 15
            Destn.Value = Join(Array(Source.Cells(g), Source.Cells(h), Source.Cells(i), Source.Cells(j)), ",")
            Set Destn = Destn.Offset(1)
          Next
        Next
      Next
    Next
    End Sub
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #22
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,912
    or faster:
    Sub Blah2()
    Set Source = Range("A1:A15")
    SceVals = Source.Value
    Set Destn = Range("C1")
    myCount = Application.WorksheetFunction.Combin(UBound(SceVals), 4)
    ReDim Results(1 To myCount, 1 To 1)
    k = 1
    For g = 1 To UBound(SceVals) - 3
      For h = g + 1 To UBound(SceVals) - 2
        For i = h + 1 To UBound(SceVals) - 1
          For j = i + 1 To UBound(SceVals)
            Results(k, 1) = Join(Array(SceVals(g, 1), SceVals(h, 1), SceVals(i, 1), SceVals(j, 1)), ",")
            k = k + 1
          Next
        Next
      Next
    Next
    Destn.Resize(myCount).Value = Results
    End Sub
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #23
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,780
    Especially designed for you:

    http://www.snb-vba.eu/VBA_Combinations_en.html

  4. #24
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,009
    Location
    Try this


    Option Explicit
    
    
    Sub C15_4()
        Const cA As Long = 65       '   use number values
        Const cO As Long = 79       '   ASCII A = 65, ASCII O = 79
        
        Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, o As Long
        
        o = 1               '   starting output row
        
        For i1 = cA To cO   '   A - L
            For i2 = i1 + 1 To cO
                For i3 = i2 + 1 To cO
                    For i4 = i3 + 1 To cO
                        ActiveSheet.Cells(o, 1).Value = Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4)
                        o = o + 1
                    Next i4
                Next i3
            Next i2
        Next i1
    
    
        MsgBox "Done"
    
    
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #25
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    2
    Location
    THANK Y'ALL SO SO SO MUCH!!!! PROBLEM SOLVED PERFECTLY. I APPRECIATE THE HELP

  6. #26
    same issue. thanks for all you guys. it's quite helpful for me so solve the problem.

Posting Permissions

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