Formula For all possible product combinations

revilo

New member
Joined
Sep 30, 2011
Messages
22
Reaction score
0
Points
0
I need an excel spreadsheet to give me all my possible product model
numbers, with the various options included. Please see the attached
spreadsheet for an example. I hope i have explained it well enough.
 

Attachments

  • Product Models.xlsx
    8 KB · Views: 183
This is becoming kind of urgent, is there nobdy that can help me?
 
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
 

Attachments

  • xlgf491-1.xls
    34.5 KB · Views: 215
Back
Top