List of combinations

Mollymyrtle

New Member
Joined
May 14, 2019
Messages
1
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
Here is some code that I found and adapted to your problem which might do it for you

Code:
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
 
Upvote 0
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

 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top