combinations generator

natsu

New Member
Joined
Apr 28, 2021
Messages
9
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hey, I am trying to find the different combinations that can be formed by choosing 11 out of 22 objects ( i.e. C(22,11) ). I have posted below the code I tried using. the problem is that this code give me all possible combinations whereas I only need different ways in which 11 objects can be chosen out of 22.


VBA Code:
items(1) = "albert"
  items(2) = "pane"
  items(3) = "drey"
  items(4) = "max"
  items(5) = "pat"
  items(6) = "sam"
  items(7) = "shay"
  items(8) = "hem"
  items(9) = "asa"
  items(10) = "stone"
  items(11) = "sunderland"
  items(12) = "christian"
  items(13) = "hare"
  items(14) = "rob"
  items(15) = "sir"
  items(16) = "jamie"
  items(17) = "gray"
  items(18) = "khan"
  items(19) = "missy"
  items(20) = "seed"
  items(21) = "afb"
  items(22) = "cole"
  Set x = ThisWorkbook.Sheets.Add
  For a = True To False
    For b = True To False
      For c = True To False
        For d = True To False
          For e = True To False
            For f = True To False
              For g = True To False
                For h = True To False
                  For i = True To False
                    For j = True To False
                      For k = True To False
                        For l = True To False
                          For m = True To False
                            For n = True To False
                              For o = True To False
                                For p = True To False
                                  For q = True To False
                                    For r = True To False
                                      For s = True To False
                                        For t = True To False
                                          For u = True To False
                                            For v = True To False
                                              z = z + 1
                                              txt = "("
                                              If a Then txt = txt & items(1) & ", "
                                              If b Then txt = txt & items(2) & ", "
                                              If c Then txt = txt & items(3) & ", "
                                              If d Then txt = txt & items(4) & ", "
                                              If e Then txt = txt & items(5) & ", "
                                              If f Then txt = txt & items(6) & ", "
                                              If g Then txt = txt & items(7) & ", "
                                              If h Then txt = txt & items(8) & ", "
                                              If i Then txt = txt & items(9) & ", "
                                              If j Then txt = txt & items(10) & ", "
                                              If k Then txt = txt & items(11) & ", "
                                              If l Then txt = txt & items(12) & ", "
                                              If m Then txt = txt & items(13) & ", "
                                              If n Then txt = txt & items(14) & ", "
                                              If o Then txt = txt & items(15) & ", "
                                              If p Then txt = txt & items(16) & ", "
                                              If q Then txt = txt & items(17) & ", "
                                              If r Then txt = txt & items(18) & ", "
                                              If s Then txt = txt & items(19) & ", "
                                              If t Then txt = txt & items(20) & ", "
                                              If u Then txt = txt & items(21) & ", "
                                              If v Then txt = txt & items(22) & ", "
                                              txt = txt & ")"
                                              txt = Replace(txt, ", )", ")")
                                              x.Cells(z, 1).Value = z
                                              x.Cells(z, 2).Value = txt
                                            Next v
                                          Next u
                                        Next t
                                      Next s
                                    Next r
                                  Next q
                                Next p
                              Next o
                            Next n
                          Next m
                        Next l
                      Next k
                    Next j
                  Next i
                Next h
              Next g
            Next f
          Next e
        Next d
      Next c
    Next b
  Next a
  MsgBox z & " combinations found.", vbInformation
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
223
@natsu, are you aware that 11 of 22 different possibilities will yield 705432 different combinations?

= COMBIN(22,11)
 

natsu

New Member
Joined
Apr 28, 2021
Messages
9
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
@natsu, are you aware that 11 of 22 different possibilities will yield 705432 different combinations?
@johnnyL yes I am aware of that. When I ran the code above I got around 1 million combinations. So I wanted to change the code so that I get only 11 of 22 combinations which is 705432😀
 

gior

New Member
Joined
Aug 8, 2011
Messages
36
Pls check code below (after "Set x= ...."). Code is slow - generates about 60K string per minute without txt calculation
VBA Code:
For i01 = 11 To 22
  For i02 = 10 To i01 - 1
    For i03 = 9 To i02 - 1
      For i04 = 8 To i03 - 1
        For i05 = 7 To i04 - 1
          For i06 = 6 To i05 - 1
            For i07 = 5 To i06 - 1
              For i08 = 4 To i07 - 1
                For i09 = 3 To i08 - 1
                  For i10 = 2 To i09 - 1
                    For i11 = 1 To i10 - 1

  Z = Z + 1
  x.Cells(Z, 1).Value = Z
 
  ' txt = items(i01) & ", " & items(i02) & ", " & items(i03) & ", " & items(i04) & ", " _
         & items(i05) & ", " & items(i06) & ", " & items(i07) & ", " & items(i08) & ", " _
         & items(i09) & ", " & items(i10) & ", " & items(i11)
  ' x.Cells(Z, 2).Value = txt
 
                    Next
                  Next
                Next
              Next
            Next
          Next
        Next
      Next
    Next
  Next
Next
 

natsu

New Member
Joined
Apr 28, 2021
Messages
9
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Pls check code below (after "Set x= ...."). Code is slow - generates about 60K string per minute without txt calculation
VBA Code:
For i01 = 11 To 22
  For i02 = 10 To i01 - 1
    For i03 = 9 To i02 - 1
      For i04 = 8 To i03 - 1
        For i05 = 7 To i04 - 1
          For i06 = 6 To i05 - 1
            For i07 = 5 To i06 - 1
              For i08 = 4 To i07 - 1
                For i09 = 3 To i08 - 1
                  For i10 = 2 To i09 - 1
                    For i11 = 1 To i10 - 1

  Z = Z + 1
  x.Cells(Z, 1).Value = Z

  ' txt = items(i01) & ", " & items(i02) & ", " & items(i03) & ", " & items(i04) & ", " _
         & items(i05) & ", " & items(i06) & ", " & items(i07) & ", " & items(i08) & ", " _
         & items(i09) & ", " & items(i10) & ", " & items(i11)
  ' x.Cells(Z, 2).Value = txt

                    Next
                  Next
                Next
              Next
            Next
          Next
        Next
      Next
    Next
  Next
Next
Hey Gior. Thanks for your response I did get 705432 outcomes but none of them had the names listed on them. Only the numbers were listed from 1 to 705432. Can you help me with this?
 

Marc L

Active Member
Joined
Apr 5, 2021
Messages
460
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
is this a question to just know the number of combinations (instant result) or to generate / list all those combinations the quickest way possible ?​
 

natsu

New Member
Joined
Apr 28, 2021
Messages
9
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi,​
is this a question to just know the number of combinations (instant result) or to generate / list all those combinations the quickest way possible ?​
Hii, it is to list all those combinations.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,982
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top