Make possible set of 5 numbers using from 3 or 4 groups

[motilulla]

Using Excel 2010

Hello,

Here below VBA solution is given by Eric W which does create all possible combinations of 5 numbers from 5 groups picking 1 number from each group.

Sub test1()
Dim UpperLeft As Range, Results As Range
Dim NumCols As Long, combos As Long, maxrow As Long, c As Long, r As Long
Dim cap() As Long, ix() As Long, output() As Variant, MyData As Variant

    Set UpperLeft = Range("B4")
    Set Results = Range("H4")
 
    NumCols = UpperLeft.End(xlToRight).Column - UpperLeft.Column + 1
    ReDim ix(1 To NumCols)
    ReDim cap(1 To NumCols)
    combos = 1
    maxrow = 0
 
    For c = 1 To NumCols
        cap(c) = UpperLeft.Offset(, c - 1).End(xlDown).Row - UpperLeft.Row
        combos = combos * cap(c)
        maxrow = IIf(maxrow > cap(c), maxrow, cap(c))
        ix(c) = 1
    Next c
    
    If combos > Rows.Count - Results.Row Then
        MsgBox "Insufficient rows to display all results"
        Exit Sub
    End If
 
    ReDim output(1 To combos, 1 To NumCols)
    MyData = UpperLeft.Offset(1).Resize(maxrow, NumCols).Value
 
    r = 1
 
NextItem:
    For c = 1 To NumCols
        output(r, c) = MyData(ix(c), c)
    Next c
    r = r + 1
 
    For c = NumCols To 1 Step -1
        ix(c) = ix(c) + 1
        If ix(c) <= cap(c) Then GoTo NextItem:
        ix(c) = 1
    Next c
 
    Results.Resize(, NumCols) = UpperLeft.Resize(, NumCols).Value
    Results.Offset(1).Resize(combos, NumCols) = output
 
End Sub

I need this VBA to finish my project analysis which can create all possible combination of 5 sets from 3 or 4 numbers groups (in case of 3 groups it has to pick from each group 3 numbers and in case of 4 groups it has to pick 2 numbers)

Here is an example with 4 group containing 3 numbers in each group to make it simpler Group columns B, C, D and E and result in the Columns H, I, J, K and L (it can be vary min 3 to max 7 numbers per group and the same min/max numbers would be in the 3 groups also)

MrExcel Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4		Group 1	Group 2	Group 3	Group 4	Group 5		n1	n2	n3	n4	n5
5		1	4	7	10			1	2	4	7	10
6		2	5	8	11			1	2	4	7	11
7		3	6	9	12			1	2	4	7	12
8
9								1	3	4	7	10
10								1	3	4	7	11
11								1	3	4	7	12
12
13								2	3	4	7	10
14								2	3	4	7	11
15								2	3	4	7	12
16
17								2	4	5	7	10
18								2	4	5	7	11
19								2	4	5	7	12
20
21								3	4	5	7	10
22								3	4	5	7	11
23								3	4	5	7	12
24
25								3	6	9	10	11
26								3	6	9	10	12
27								3	6	9	11	12
28
29
30
31
32
Sheet Modified

Please suggest VBA, I want which can work with Excel version 2000 also.

Thank you all.

Regards,
Moti

 

[motilulla]

Hello, I did search in MrExcel with using lot of text in different way about lottery combinations by groups but no luck searched also in Google but did not find any solution.

Please need helping hand how can make a VBA which at least can make combinations of 5 set from “4 groups” picking 2 numbers from each group and 3 number from others 3 groups which will build a set of 5 numbers. Don’t know how much will be generated as per given example in the post#1

Thank you all in advance

Regards,
Moti

 

[motilulla]

BUMP

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Make possible set of 5 numbers using from 3 or 4 groups
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[motilulla]

Hello Fluff, I am sorry.

Do you mean I post the whole question with details along with the cross post link?

Thank you

My Best Regards,
Moti

 

[Fluff]

No I mean that in future you need to let us know you have posted the same question elsewhere.

 

[motilulla]

No I mean that in future you need to let us know you have posted the same question elsewhere.

Ok Fluff, it is perfect i will follow-up Forum Rules.

This time i apologize and thank you for letting me know it is fair to inform here

My Best Regards,
Moti

 

[motilulla]

Bump, please help

 

[motilulla]

Hello @Eric W,

Please can you help to create to get all possible combination of 5 numbers out of 4 groups picking minimum 2 numbers from the each or 4 groups.

I am sure after 150 views this question has no answer that means it is complicate task.

Please help. I have had intense search but did not find any answer yet.

Regards,
Moti

 

[motilulla]

Hello @Eric W,

Please can you help to create to get all possible combination of 5 numbers out of 4 groups picking minimum 2 numbers from the each or 4 groups.

I am sure after 150 views this question has no answer that means it is complicate task.

Please help. I have had intense search but did not find any answer yet.

Regards,
Moti

Hello,

I think perhaps my question is wrong please ignore I will re-think and summit again correcting it.

Sorry for the trouble thanks everyone for looking in to it.

Kind Regards,
Moti