Combinations macro

[Louisa Coles]

Hi Ken,

i am Also looking for the same thing for 11 products, i need all possible combinations with different digits.

ie

1259
123
567
12345678

i Hope this makes sense

The attached example file uses VBA to do it.

Commented code as follows:

Sub Possibilities()
    Dim sModel As String
    Dim cl As Range
    Dim rngModels As Range
    Dim rngCatA As Range
    Dim lClean As Long
    Dim lColA As Long
    Dim lColB As Long
    Dim lColC As Long
    Dim lColD As Long
    With Worksheets("Sheet1")
        'Range of model numbers to prepend
        Set rngModels = .Range("A2:A3")
        
        'First cell in data table
        Set rngCatA = .Range("B8")
        
        'Set up target list area
        .Range("B16:B" & .Range("B" & .Rows.Count).End(xlUp).Row).ClearContents
        .Range("B16").Value = "Model Number"
        'Create possibilities
        For Each cl In rngModels
            For lColA = 0 To 3
                For lColB = 0 To 3
                    For lColC = 0 To 3
                        For lColD = 0 To 3
                            'Generate raw possibilities
                            sModel = cl.Value & "/" & _
                                     rngCatA.Offset(lColA, 0) & "/" & _
                                     rngCatA.Offset(lColB, 1) & "/" & _
                                     rngCatA.Offset(lColC, 2) & "/" & _
                                     rngCatA.Offset(lColD, 3)
                                  
                            'Strip any instances of // (from blank quartets)
                            For lClean = 1 To 4
                                sModel = Replace(sModel, "//", "/")
                            Next lClean
                            
                            'Remove trailing / if necessary (blank final quartet)
                            If Right(sModel, 1) = "/" Then sModel = Left(sModel, Len(sModel) - 1)
                            
                            'Place value in cell
                            .Range("B" & .Range("B" & .Rows.Count).End(xlUp).Row).Offset(1, 0).Value = sModel
                        Next lColD
                    Next lColC
                Next lColB
            Next lColA
        Next cl
    End With
End Sub

[Ken Puls]

Hi there,

I've moved your post to a new thread, rather than tack it on the previous one.

Could you be a bit more explicit about what you're looking to do here? Maybe even provide a sample for a single product that shows exactly what you'd expect the output to be? I'm not sure I'm 100% confident about what you're asking for the output here.

[Louisa Coles]

Hi Ken, i need to know the possible combination of 1-11

Going from single digit to two digits to 3 digits and so on up to 11
We are creating a spread sheet where the numbers will represent a product for a service,

They will have a choice of 11 products for one service or the could chose 3 of the 11 products or 7 or 4.

I hope this makes more sense.

Louisa

[Ken Puls]

I'm still a bit confused here. Are 1-11 the number of a specific product, and we're trying to figure out what combination of products might be ordered, or are they individual values and you're trying to create product numbers?

How is the output supposed to look? 1,2,3 or just a string of numbers with no commas? The reason I'm asking is that mixing a 10 and 11 into this is going to create duplicate values if it's the latter.

[Louisa Coles]

Hi Ken,

so the macro we need is to show all possible combinations of 1-11, so for one service there will be 11 choices of add on's to choose but they can choose any number of add on from 1- all eleven.


so we need something to look like this

SERVICE. OPTION

service. 1 2 5 8 10
service. 2 9
service. 3 4 5 8 10 11
service. 4 8 11

and so on, I hope this makes more sense so they have 1 service and a choice of 11 options but they can have any 11 of the 11 options so from 1 digit combinations up to 11.

[Louisa Coles]

book 2.xlsx

Hi Ken,

mi have attached a workbook with what i mean i hope this makes more sense.

Thanks

Louisa