create list of all possible combinations of 5 lists

[palaeontology]

I have the following multi-column table ..

In other words, I have 5 different types of head, 5 different types of Neck&Arms, etc etc

I'd like to create a single-column list of all the 15 625 combinations of Head, Neck&Arms, Torso, Thighs, Shins and Feet that are possible

example, the list might start with ...

Is there an easy way to generate this ?

Kind regards,

Chris

 

[palaeontology]

never mind ... i figured it out ... if I place 111111 in cell H1, then the following formula will generate all 15 625 combinations when dragged down ....

=IF(RIGHT(H1,5)="55555",H1+55556,IF(RIGHT(H1,4)="5555",H1+5556,IF(RIGHT(H1,3)="555",H1+556,IF(RIGHT(H1,2)="55",H1+56,IF(RIGHT(H1,1)="5",H1+6,H1+1)))))

 

[glfiedler]

I see you did it by brute force before I finished my code. Here it is anyway:

Sub combo_column()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim p As Integer
Dim num As Long
Dim myrow As Long
num = 111110
myrow = 0
For p = 1 To 5
    For n = 1 To 5
        For m = 1 To 5
            For k = 1 To 5
                For j = 1 To 5
                    For i = 1 To 5
                    num = num + i
                    myrow = myrow + 1
                    Worksheets("Sheet1").Range("A" & myrow) = num
                    Next i
                Next j
            Next k
        Next m
    Next n
Next p
End Sub

 

[MoshiM]

I wrote a macro but you already solved your problem.
Edit :someone else did too, while I didn't refresh the page

Sub FivebyFive()

Dim WS() As Variant, NumbersC As Object, OneC As Variant, TwoC As Variant, ThreeC As Variant, _
FourC As Variant, FiveC As Variant, A As Long, B As Long, C As Long, D As Long, E As Long, F As Long, Escape As Boolean, Target_R As Range

Set NumbersC = CreateObject("Scripting.Dictionary")

Set Target_R = ThisWorkbook.ActiveSheet.Range("G1")

WS = ActiveSheet.UsedRange.Value2

With WorksheetFunction
    OneC = .Transpose(.Index(WS, 0, 1))
    TwoC = .Transpose(.Index(WS, 0, 2))
    ThreeC = .Transpose(.Index(WS, 0, 3))
    FourC = .Transpose(.Index(WS, 0, 4))
    FiveC = .Transpose(.Index(WS, 0, 5))
    sixc = .Transpose(.Index(WS, 0, 6))
End With

A = 1
B = 1
C = 1
D = 1
E = 1
F = 1

Do While Escape = False
  
    NumbersC.Add OneC(A) & TwoC(B) & ThreeC(C) & FourC(D) & FiveC(E) & sixc(F), Empty

  F = F + 1

  If F = 6 Then
  
      F = 1
      E = E + 1
    
      If E = 6 Then
    
          E = 1
          D = D + 1
        
          If D = 6 Then
        
              D = 1
              C = C + 1
            
              If C = 6 Then
            
                  C = 1
                  B = B + 1
                
                  If B = 6 Then
                
                      B = 1
                      A = A + 1
                    
                      If A = 6 Then
                    
                        Escape = True
                      
                      End If
                    
                  End If
                
              End If
            
          End If
        
      End If
  
    End If
  
Loop

Target_R.Resize(NumbersC.Count, 1) = WorksheetFunction.Transpose(NumbersC.KEYS)

'Debug.Print NumbersC.Count

End Sub

 

[glfiedler]

Please ignore my code in post #3. Each loop needs to set a new value of num before proceeding with the inner loop. I will gladly fix the code if you want.

 

[glfiedler]

Here is the corrected version of my solution.

Sub combo_column()
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim n As Long
Dim p As Long
Dim num As Long
Dim myrow As Long
num = 0
myrow = 0
For p = 100000 To 500000 Step 100000
    For n = 10000 To 50000 Step 10000
        For m = 1000 To 5000 Step 1000
            For k = 100 To 500 Step 100
                For j = 10 To 50 Step 10
                    For i = 1 To 5
                        num = p + n + m + k + j + i
                        myrow = myrow + 1
                        Worksheets("Sheet1").Range("A" & myrow) = num
                    Next i
                Next j
            Next k
        Next m
    Next n
Next p
End

 

[Robin121]

Now, can you rewrite this to show any combinaton of two persons from a list? Much appreciated, thank you.

 

[glfiedler]

Robin, I do not understand what you mean. Does the previously created column of over 15,000 possible combinations represent one person? Wouldn't the second person have the same list of possible combinations? If that is the case then every one of the 15,625 combos for person 1 could pair up with each of person 2's combos. This would result in 244,140,625 combos. You would end up with a table with 15,625 rows by 15,626 columns. Is that what are you trying to accomplish?

 

[Robin121]

Robin, I do not understand what you mean. Does the previously created column of over 15,000 possible combinations represent one person? Wouldn't the second person have the same list of possible combinations? If that is the case then every one of the 15,625 combos for person 1 could pair up with each of person 2's combos. This would result in 244,140,625 combos. You would end up with a table with 15,625 rows by 15,626 columns. Is that what are you trying to accomplish?

I was merely hitching a ride on this topic.
Trying to find code that will show possible combinations of persons and their classifications. For example: A, B and C have classification 4, D and E have classification 5 and F and G have classification 6. How many possible combinations for three tennis doubles, with the restriction that the accumulated combinations have to play in descending order, 4/4 is considered stronger than 4/6.

 

[Robin121]

I was merely hitching a ride on this topic.
Trying to find code that will show possible combinations of persons and their classifications. For example: A, B and C have classification 4, D and E have classification 5 and F and G have classification 6. How many possible combinations for three tennis doubles, with the restriction that the accumulated combinations have to play in descending order, 4/4 is considered stronger than 4/6.

Correction: ascending order