Wheeling formula system.

[serge]

Hi,

I'm looking for a formula that will create all possible set combinations of 5 numbers, with number from row 4, 5, 6, 7 and 8 and letter C:J.


The numbers will be from row 4 in cells C4:J4 then for row 5 in cells C5:J5 and so on, last row 8 in C8:J8.

Not all cells from each rows will have numbers in it, it will varies, some row will have 2 numbers other 6 numbers

I'm looking for a formula that will adjust if cells will have numbers in it or not.

The results sets return must start in cell C11.


Thank you
Serge.

 

[serge]

Does anyone have an idea ?

 

[mikerickson]

Could you give an example of some inputs and part of the desired result.

 

[serge]

Hi Mikerickson,

Yes, for example if I have I have the following :

Row 4 : 3,7
Row 5 : 10
Row 6 : 21,25,30
Row 7 : 42,45
Row 8 : 53,56

My Sets combinations would be as follow starting in cells C11:G11 :

3 10 21 42 53
3 10 21 42 56
3 10 21 45 53
3 10 21 45 56

3 10 25 42 53
3 10 25 42 56
3 10 25 45 53
3 10 25 45 56

3 10 30 42 53
3 10 30 42 56
3 10 30 45 53
3 10 30 45 56

and then the same with the numbers 7 and so on if there will be more numbers :

7 10 21 42 53
7 10 21 42 56
7 10 21 45 53
7 10 21 45 56

7 10 25 42 53
7 10 25 42 56
7 10 25 45 53
7 10 25 45 56

7 10 30 42 53
7 10 30 42 56
7 10 30 45 53
7 10 30 45 56

But in any row 4,5,6,7,8 it could be more numbers in it or less it will depends on what I need !

I hope I explained it right ?

Thank you.
Serge.

 

[mikerickson]

I think this will do what you want.

Sub test()
    Dim colIndex(1 To 5) As Long
    Dim ColMax(1 To 5) As Long
    Dim arrData As Variant
    Dim valArray(1 To 5) As Long
    Dim flag As Boolean
    Dim i As Long, j As Long, Pointer As Long
    
    For i = 1 To 5: colIndex(i) = 1: Next i
    
    arrData = Range("C4:J8").Value
    
    For i = 1 To 5
        Pointer = 0
        For j = 1 To 8
            If Val(arrData(i, j)) > 0 Then
                Pointer = Pointer + 1
                arrData(i, Pointer) = arrData(i, j)
            End If
        Next j
        ColMax(i) = Pointer
    Next i
    
    j = 11
    Do
        For i = 1 To 5
            valArray(i) = arrData(i, colIndex(i))
        Next i
        
        Cells(j, 3).Resize(1, 5).Value = valArray
        j = j + 1
        For i = 5 To 1 Step -1
            colIndex(i) = colIndex(i) + 1
            If ColMax(i) < colIndex(i) Then
                colIndex(i) = 1
                If i = 1 Then flag = True
            Else
                Exit For
            End If
        Next i
    Loop Until flag
End Sub

 

[serge]

Thank you so much, Mikerickson, that exactly what I needed, I really appreciate it.
Serge.

 

[serge]

The code run very slow is it because my excel file is over 63Mb and or is there something that could be done to make it run faster ?
Serge.

 

[mikerickson]

There are tweeks that could speed it up, but an "all combinations" program will take a very long time.
The first one would be to toggle Appliciation.ScreenUpdating.

 

[serge]

OK, thank you, Im gone create a new excel workbook just for the combinations it may work faster ?
Thank you anyway for your code , that really help me.
Serge.