Make possible set of 5 numbers using 4 groups.

motilulla

Well-known Member
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

 * 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

Attachments

• Set of 5 num from 4 groups.png
13.2 KB · Views: 8

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

deleted

Last edited:

footoo

Well-known Member
Maybe :
=(5*5*5*4)*COMBIN(5,2)=5000

motilulla

Well-known Member
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

Well-known Member

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

motilulla

Well-known Member
Perhaps someone else will work it out. Here's a link that should help :
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

Well-known Member

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

Kind Regards,
Moti

BUMP

Phuoc

Well-known Member
The number of combinations will be 10000:

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

motilulla

Well-known Member
The number of combinations will be 10000:

=5*5*5*5*(5+5+5+5-4)
Phuoc, May could be 10000 but I am not sure, as per @footoo it must be 5000 I need a VBA which can generate all possible combination condition pick from 4 groups min 1 and max 2 numbers set of 5 numbers.

May be some one will help I am sure it is bit hard task to generate limiting numbers in each group.

Kind Regards,
Moti

Replies
5
Views
249
Replies
17
Views
276
Replies
2
Views
405
Replies
4
Views
314
Replies
1
Views
178

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,795
Messages
5,772,282
Members
425,750
Latest member
johobie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back