Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 68

Thread: find all combinations (no repeats)

  1. #21
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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 Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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 Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Especially designed for you:

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

  4. #24
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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.

  7. #27
    Hi Paul,

    I was trying to figure out an Excel VBA for combination of 6 columns of numbers. Lets say columns A-F contain numbers. I want to generate all the combination of these 6 columns without repetition and considering the criteria below for generated combination( assuming the combinations are stored in COLUMNS G-L):

    G<H<I<J<K<L

    I appreciated if you could help on this.

  8. #28
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    There is only one combination with those criteria.
    All you need to do is sort horizontally.
    p45cal
    Everyone: 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.

  9. #29
    Quote Originally Posted by p45cal View Post
    There is only one combination with those criteria.
    All you need to do is sort horizontally.
    Hi p45cal,

    I am not sure if I am following you. each columns has about 35-45 numbers. How can you get all the combinations as it is required with the method you mentioned?

  10. #30
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    OK. Maybe I've misunderstood; best attach a workbook.
    p45cal
    Everyone: 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.

  11. #31
    Quote Originally Posted by p45cal View Post
    OK. Maybe I've misunderstood; best attach a workbook.
    p45cal,

    Please see attach workbook as an example.

    Thanks
    Attached Files Attached Files

  12. #32
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You realise there are about 6.25 million combinations that meet your criteria?
    (21 million+ ignoring criteria)

    How do you want the results, bearing in mind there are only about 1 million rows on a sheet?
    p45cal
    Everyone: 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.

  13. #33
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    How good is your example?

    1. I can see that the Green is acceptable -- G<H<I<J<K<L

    2. I can see that the Orange is not acceptable -- G>H

    3. Where did the Blue come from?

    Capture.JPG


    Lets say columns A-F contain numbers. I want to generate all the combination of these 6 columns without repetition and considering the criteria below for generated combination (assuming the combinations are stored in COLUMNS G-L): G<H<I<J<K<L
    I don't see where 'All Combinations' comes in

    For example

    1 10 24 39 45 55


    can be arranged 720 ways (6!) and there's only 1 way G<H<I<J<K<L


    Likewise

    9 6 20 40 41 51

    can be arranged 720 ways and 6-9-20-40-41-51 would be the answer

    It sort of / almost / kind of looks like you just want to take 6 numbers and see if G<H<I<J<K<L, returning a Yes/No answer
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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

  14. #34
    Quote Originally Posted by p45cal View Post
    You realise there are about 6.25 million combinations that meet your criteria?
    (21 million+ ignoring criteria)

    How do you want the results, bearing in mind there are only about 1 million rows on a sheet?
    Thanks p45cal,

    What I need is a VBA code to generate all those combination.

    Thanks

  15. #35
    Quote Originally Posted by Paul_Hossler View Post
    How good is your example?

    1. I can see that the Green is acceptable -- G<H<I<J<K<L

    2. I can see that the Orange is not acceptable -- G>H

    3. Where did the Blue come from?

    Capture.JPG




    I don't see where 'All Combinations' comes in

    For example

    1 10 24 39 45 55


    can be arranged 720 ways (6!) and there's only 1 way G<H<I<J<K<L


    Likewise

    9 6 20 40 41 51

    can be arranged 720 ways and 6-9-20-40-41-51 would be the answer

    It sort of / almost / kind of looks like you just want to take 6 numbers and see if G<H<I<J<K<L, returning a Yes/No answer
    Paul,

    I attached another file that might make the concept clearer for you. For the sake of simplicity I consider only 4 columns (note: this is an example , I need a VBA code to work for 6 columns).

    Thanks

    thanks
    Attached Files Attached Files

  16. #36
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by NIMANIMA50 View Post
    What I need is a VBA code to generate all those combination.
    I've done that, so again:
    Quote Originally Posted by p45cal View Post
    You realise there are about 6.25 million combinations that meet your criteria?
    How do you want the results, bearing in mind there are only about 1 million rows on a sheet?
    Last edited by p45cal; 03-14-2021 at 01:53 AM.
    p45cal
    Everyone: 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.

  17. #37
    Quote Originally Posted by p45cal View Post
    I've done that, so sgain:
    p45cal ,

    can we split the result in 7 or or worksheets/ workbooks?

    thanks

  18. #38
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the attached is a button in the vicinity of cell J8.
    Here, it takes the code about 5 seconds to get the 6.25 million results, about 40 secs to write them to the sheets.

    Note that there is nothing in the code to eliminate repeats; at the moment there are none because none of the individual source columns contain any duplicates. If duplicates do occur in the original columns the code will need a tweak.
    Attached Files Attached Files
    Last edited by p45cal; 03-14-2021 at 04:36 AM.
    p45cal
    Everyone: 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.

  19. #39
    Quote Originally Posted by p45cal View Post
    In the attached is a button in the vicinity of cell J8.
    Here, it takes the code about 5 seconds to get the 6.25 million results, about 40 secs to write them to the sheets.
    THANKS A LOT P45CAL. Seems working perfectly.

  20. #40
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    What on earth are you going to do with 6.25 million rows of data?!
    p45cal
    Everyone: 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.

Posting Permissions

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