Make possible set of 5 numbers using 4 groups.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,073
Office Version
  1. 2003 or older
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

*ABCDEFGHIJKLM
1
2
3
4Group 1Group 2Group 3Group 4n1n2n3n4n5
5117274413172744
63183245117182744
75223647112223247
882442481224364248
912254350122364748
10825273648
11522324247
12
13
14
15
16

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Set of 5 num from 4 groups.png
    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.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,345
Office Version
  1. 2016
Platform
  1. Windows
deleted
 
Last edited:

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,073
Office Version
  1. 2003 or older
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 :)
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,073
Office Version
  1. 2003 or older

ADVERTISEMENT

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
 

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
537
Office Version
  1. 2016
The number of combinations will be 10000:

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

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,073
Office Version
  1. 2003 or older
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.

Thank you for your replay

Kind Regards,
Moti :)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top