Louisa Coles
New member
- Joined
- Oct 3, 2013
- Messages
- 4
- Reaction score
- 0
- Points
- 0
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
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:
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