All possible combinations

uneiman

New Member
Joined
Jul 27, 2002
Messages
44
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?
 

Some videos you may like

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
Joined
May 28, 2002
Messages
11,028
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
Joined
Jun 3, 2006
Messages
550

ADVERTISEMENT

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

scu77

New Member
Joined
Sep 14, 2005
Messages
22
It's like worldcup team chart !! :biggrin:


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
Joined
Sep 14, 2005
Messages
22
"scu77 ... is there a link to that tool you posted which is in English?"


hey :biggrin: jmckeone!

Do you have any problem?
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Found answer. Link you posted for Combo.xla only works in Internet Explorer. I have it now. Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,367
Members
410,911
Latest member
AniEx
Top