All possible combinations

[uneiman]

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?

 

[Oaktree]

See:

http://www.mrexcel.com/board2/viewtopic.php?t=179001

 

[scu77]

hi...


Combination and Permutation's core code by

"Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc "



Use Combo.xla

http://www.geocities.jp/scu_7_7/xl/Combo.zip

 

[tusharm]

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]

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

 

[scu77]

It's like worldcup team chart !!


Copy this 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

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]

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


hey jmckeone!

Do you have any problem?

 

[jmckeone]

Found answer. Link you posted for Combo.xla only works in Internet Explorer. I have it now. Thank you.