Make possible set of 5 numbers using 4 groups.

[motilulla]

Hello,

I have got 4 groups of 5 numbers as follow...
Group1-In the column "B" with 5 numbers
Group2-In the column "C" with 5 numbers
Group3-In the column "D" with 5 numbers
Group4-In the column "E" with 5 numbers

In fact using 20 numbers and making sets of numbers there must be total combinations =COMBIN(20,5) = 15.504

But I want to limit that the to make the set of 5 numbers it must pick 1 number from any of 3 groups and the 2 numbers from any of 1 group... I am not sure how much total combinations will be produce using this way

Please see the attached image example for more information

Please suggest VBA

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4		Group 1	Group 2	Group 3	Group 4		n1	n2	n3	n4	n5
5		1	17	27	44		1	3	17	27	44
6		3	18	32	45		1	17	18	27	44
7		5	22	36	47		1	12	22	32	47
8		8	24	42	48		12	24	36	42	48
9		12	25	43	50		1	22	36	47	48
10							8	25	27	36	48
11							5	22	32	42	47
12
13
14
15
16

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Phuoc]

For example first combination is generated... 1,17,27,44,3 could it be in order as follow ... 1,3,17,27 and 44

Try this code:

Public Sub Five_numbers()
Dim dic As Object
Dim arrData, arrDec
Dim isUni As Boolean
Dim arr, arrB
Dim ID As String
Dim i1&, i2&, i3&, i4&, j&, c&, u&, k&
Set dic = CreateObject("Scripting.Dictionary")
ReDim arrDec(1 To 10000, 1 To 5)
ReDim arr(1 To 5)
arrData = Range("A2:D6").Value ' Change here

u = UBound(arrData, 1)
For i1 = 1 To u
    arr(1) = arrData(i1, 1)
    For i2 = 1 To u
        arr(2) = arrData(i2, 2)
        For i3 = 1 To u
            arr(3) = arrData(i3, 3)
            For i4 = 1 To u
                arr(4) = arrData(i4, 4)
                For c = 1 To 4
                    For j = 1 To u
                        isUni = False
                        Select Case c
                        Case 1
                            If j = i1 Then isUni = True
                        Case 2
                            If j = i2 Then isUni = True
                        Case 3
                            If j = i3 Then isUni = True
                        Case 4
                            If j = i4 Then isUni = True
                        End Select
                            If isUni = False Then
                            arr(5) = arrData(j, c)
                            arrB = Mysort(arr)
                           ID = arrB(1) & "|" & arrB(2) & "|" & arrB(3) & "|" & arrB(4) & "|" & arrB(5)
                            If dic.exists(ID) = False Then
                                k = k + 1
                                dic.Item(ID) = k
                                arrDec(k, 1) = arrB(1)
                                arrDec(k, 2) = arrB(2)
                                arrDec(k, 3) = arrB(3)
                                arrDec(k, 4) = arrB(4)
                                arrDec(k, 5) = arrB(5)
                            End If
                            End If
                    Next j
                Next c
            Next i4
        Next i3
    Next i2
Next i1
Range("G1").Resize(k, 5).Value = arrDec
MsgBox k
End Sub
Function Mysort(ByVal a As Variant) As Variant
Dim i As Long, ii As Long, s As Variant
For i = 1 To 4
    For ii = i + 1 To 5
        If a(i) > a(ii) Then
            s = a(i)
            a(i) = a(ii)
            a(ii) = s
        End If
    Next ii
Next i
Mysort = a
End Function

 

[footoo]

deleted

 

[footoo]

Maybe :
=(5*5*5*4)*COMBIN(5,2)=5000

 

[motilulla]

Maybe :
=(5*5*5*4)*COMBIN(5,2)=5000

footoo, the formula you build it looks logical and may be it is correct too. Thank you for the help.

Do you help how to create these 5000 combinations with the given scenario?

Kind Regards,
Moti

 

[footoo]

Perhaps someone else will work it out. Here's a link that should help :

excel vba list all possible combinations - Google Search

www.google.com

 

[motilulla]

Perhaps someone else will work it out. Here's a link that should help :

excel vba list all possible combinations - Google Search

www.google.com

footoo, thank you for the replay and providing links I will check them if I get success will share the results if not will ask for the help

Good luck and have a great weekend

Kind Regards,
Moti

 

[motilulla]

footoo, thank you for the replay and providing links I will check them if I get success will share the results if not will ask for the help

Good luck and have a great weekend

Kind Regards,
Moti

Hello, to everyone I did google search and also search in the MrExcel forums but did not find any VBA, which could generate combination, as I need.

Basically I need VBA, which can restrict to pick minimum 1 and maximum 2 numbers from the each of the 4 groups so this way it could generate 5000 combinations with set of 5 numbers. as show in the post#1

Please expert's need your help is highly appreciated.

Kind Regards,
Moti

 

[motilulla]

BUMP

 

[Phuoc]

The number of combinations will be 10000:

=5*5*5*5*(5+5+5+5-4)