List of combinations

[Mollymyrtle]

Hi all
need some help on a formula for a crochet pattern.
I have 6 colours and on each piece the colour can be used only once.
there are 4 rows on each piece.
Row 1 is a single colour, rows 2 & 3 have 2 colours each and the 4th row is also a single colour.
eg
row 1 - yellow
row 2 - green and blue
row 3 - purple and pink
row 4 - rust

All colours can only be used once.

so I need each piece to have a different combination of colours.
Fo yellow centre (row 1) there are 8 options I can see for row 2 and each of these have 8 options for row 3 with the last row just having 5.

Is is there a formula to get each unique combination for each coloured centre?

thanks in advance.

Molly

 

[glfiedler]

Molly,
I do not think formulas in a worksheet are going to work. I have started some VBA coding but it may take a bit of time. Please download my preliminary workbook (no coding yet) which shows the table of possible row colors. I manually calculated the first 30 patterns. I only did this to understand the logic flow so I can write some code. I will let you know if I get something that works.
https://app.box.com/s/um4xqccqy1nss7l67u6l7m9ps319tzvu

 

[theBardd]

Here is some code that I found and adapted to your problem which might do it for you

Function ListPermut()
Const num_items As Long = 6
Dim mtxItems() As Variant
Dim vecColours As Variant
Dim vecSort() As Long
Dim num_perms As Long
Dim c As Long, r As Long, y() As Long
Dim temp As Long, temp1 As Long
Dim i As Long, d As Long
    With ActiveSheet
    
        .Columns("A:E").ClearContents
            
        vecColours = Array("yellow", "green", "Blue", "purple", "pink", "rust")
    
        num_perms = WorksheetFunction.Permut(num_items, num_items)
         
        ReDim mtxItems(1 To num_perms, 1 To num_items)
         
        For c = 1 To num_items
        
          mtxItems(1, c) = c
        Next c
         
        'create a matrix of all combinations of numbers 1...num_items
        
        For r = 2 To num_perms
        
            ' 1. Find the first smaller number mtxItems(r-1, c-1)<mtxItems(r-1,c)
            For c = num_items To 1 Step -1
            
                If mtxItems(r - 1, c - 1) < mtxItems(r - 1, c) Then
                
                    temp = c - 1
                    Exit For
                End If
            Next c
        
            ' Copy values from previous row
            For c = num_items To 1 Step -1
            
                mtxItems(r, c) = mtxItems(r - 1, c)
            Next c
            
            ' 2. Find a larger number than mtxItems(r-1, temp)as far to the right as possible
            For c = num_items To 1 Step -1
                  
                If mtxItems(r - 1, c) > mtxItems(r - 1, temp) Then
                    temp1 = mtxItems(r - 1, temp)
                    mtxItems(r, temp) = mtxItems(r - 1, c)
                    mtxItems(r, c) = temp1
                      
                    ReDim y(num_items - temp)
                    i = 0
                    For d = temp + 1 To num_items
                        
                        y(i) = mtxItems(r, d)
                        i = i + 1
                    Next d
                    
                    i = 0
                    For d = num_items To temp + 1 Step -1
                        mtxItems(r, d) = y(i)
                        i = i + 1
                    Next d
                      
                    Exit For
                End If
            Next c
        Next r
    
        'map matrix of numbers to colours
        For r = 1 To num_perms
    
            mtxItems(r, 1) = vecColours(mtxItems(r, 1) - 1)
            mtxItems(r, 2) = vecColours(mtxItems(r, 2) - 1) & " & " & vecColours(mtxItems(r, 3) - 1)
            mtxItems(r, 3) = vecColours(mtxItems(r, 4) - 1) & " & " & vecColours(mtxItems(r, 5) - 1)
            mtxItems(r, 4) = vecColours(mtxItems(r, 6) - 1)
        Next r
        
        .Range("A1:D1").Value = Array("Row 1", "Row 2", "Row 3", "Row 4", "Sort")
        .Range("A2").Resize(num_perms, 4) = mtxItems
        With .Range("E1").Resize(num_perms)
            
            .Formula = "=RandBetween(1, 720)"
            .Value = .Value
        End With
        
        .Range("A1").Resize(num_perms + 1, 5).Sort key1:=.Range("E1"), Order1:=xlAscending, Header:=xlYes
        .Columns("E").Delete
        .Columns("A:D").AutoFit
    End With
End Function

 

[glfiedler]

Molly, I have written VBA routines to calculate your combinations. Please download my workbook.
There seems to be 180 combinations. You can select column K and delete its contents. Then press the command button. The combinations will be calculated and then shown in column K. Let me know if you need any changes.
https://app.box.com/s/o8c9wkbqs9e2hzwklq9spb9uvysjn3q4