# 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

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
@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

##### New Member

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

#### Marc L

##### Active 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 ?​

#### 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.

Replies
3
Views
136
Replies
1
Views
193
Replies
0
Views
122
Replies
15
Views
228
Replies
13
Views
176

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.

### 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