Results 1 to 6 of 6

Thread: Combinations macro

  1. #1

    Combinations macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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



    Quote Originally Posted by Ken Puls View Post
    The attached example file uses VBA to do it.

    Commented code as follows:
    Code:
    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.

  6. #6

    Urgent

    book 2.xlsx

    Hi Ken,

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

    Thanks

    Louisa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •