# combinations generator

#### natsu

##### New Member
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"
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### johnnyL

##### Well-known Member
@natsu, are you aware that 11 of 22 different possibilities will yield 705432 different combinations?

= COMBIN(22,11)

#### natsu

##### New Member
@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
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

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?

#### gior

##### New Member
@natsu, did you uncomment txt calculation and filling of column 2 - x.Cells(Z, 2)?

• natsu

#### natsu

##### New Member

@natsu, did you uncomment txt calculation and filling of column 2 - x.Cells(Z, 2)?
AHH missed that. Thanks a lot @gior

#### gior

##### New Member
• natsu

#### Marc L

##### Banned User
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
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. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,168,158
Messages
5,857,707
Members
431,891
Latest member
shirazx3 ### 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.

### Which adblocker are you using?    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

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