# All possible combinations

#### uneiman

##### New Member
Is there a way for Excel to LIST all possible combinations of a given range of letters or numbers?
Example:

Team A plays Team B
Team C plays Team D
Team E plays Team F

A&B can not be in the same combination, just like C&D and E&F can not be, because only one of the teams going head to head can win.

I know that there are 8 possible combinations:
ACE, ADE, ADF, ACF, BDF, BDE, BCE, BCF

So can Excel produce this list if I reference the team names in any given cells?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### tusharm

##### MrExcel MVP
I am reasonably sure I'd posted code on this subject or read someone else's code that worked well and decided not to create my own. You may want to search this forum or the XL newsgroups. Those are the only two cyber-places where I "hang out."

Myrna's excellent code -- that others have pointed you to -- won't work, at least I don't think it will. You want to select one item from each of several buckets. Myrna picks all items from a different bucket.

Is there a way for Excel to LIST all possible combinations of a given range of letters or numbers?
Example:

Team A plays Team B
Team C plays Team D
Team E plays Team F

A&B can not be in the same combination, just like C&D and E&F can not be, because only one of the teams going head to head can win.

I know that there are 8 possible combinations:
ACE, ADE, ADF, ACF, BDF, BDE, BCE, BCF

So can Excel produce this list if I reference the team names in any given cells?

#### jmckeone

##### Well-known Member

scu77 ... is there a link to that tool you posted which is in English?

#### scu77

##### New Member
It's like worldcup team chart !!

Copy this code

Code:
``````Sub SampleCode()

Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
Dim intD As Integer

Dim varArray_A As Variant
Dim varArray_B As Variant
Dim varArray_C As Variant

Dim varTemp As Variant
Dim shtSheet As Worksheet

Set shtSheet = ActiveSheet

varArray_A = Array("A", "B")
varArray_B = Array("C", "D")
varArray_C = Array("E", "F")

For intA = 0 To 1
For intB = 0 To 1
For intC = 0 To 1
varTemp = varArray_A(intA) & varArray_B(intB) & varArray_C(intC)
With shtSheet.Range("a1")
.Offset(intD, 0) = varTemp
intD = intD + 1
End With
Next
Next
Next
End Sub``````

or

Code:
``````Sub SampleCode()

Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
Dim intD As Integer
Dim intE As Integer

Dim varArray_A As Variant
Dim varArray_B As Variant
Dim varArray_C As Variant
Dim varArray_D As Variant

Dim varTemp As Variant
Dim shtSheet As Worksheet

Set shtSheet = ActiveSheet

varArray_A = Array("A", "B")
varArray_B = Array("C", "D")
varArray_C = Array("E", "F")
varArray_D = Array("G", "H")

For intA = 0 To 1
For intB = 0 To 1
For intC = 0 To 1
For intE = 0 To 1
varTemp = varArray_A(intA) & varArray_B(intB) & varArray_C(intC) & varArray_D(intE)
With shtSheet.Range("a1")
.Offset(intD, 0) = varTemp
intD = intD + 1
End With
Next
Next
Next
Next
End Sub``````

#### scu77

##### New Member
"scu77 ... is there a link to that tool you posted which is in English?"

hey jmckeone!

Do you have any problem?

#### jmckeone

##### Well-known Member
Found answer. Link you posted for Combo.xla only works in Internet Explorer. I have it now. Thank you.

Replies
7
Views
152
Replies
3
Views
269
Replies
0
Views
99
Replies
5
Views
124
Replies
9
Views
89