i have 6 columns of numbers, each with different amounts. i need all the possible combinations of these where 3 numbers picked from the 1st column, then 1, 4, 1, 4 and 4 picked in the following columns. but no numbers can be repeated in their respective columns, so in the 1st column for example say the number 2 is in it. when it picks 3 numbers from that column, it can only pick number 2 once.
please explain like i know nothing.
Any help much appreciated. thanks.
Can you provide an example workbook?
Entia non sunt multiplicanda sine necessitate
some data attached. just want the combinations of the ID columns. like i said before 3 from the 1st then 1, 4, 1, 4 and 4.
I don't understand that part.but no numbers can be repeated in their respective columns, so in the 1st column for example say the number 2 is in it. when it picks 3 numbers from that column, it can only pick number 2 once.
However, for the rest of it, there's a combinatorial expolosion.
In the 1st section, there are 98 names. There are 152,096 ways to choose 3 of them (i.e., =COMBIN(98,3))
Similarly, there are:
35 ways to choose 1 name from the 35 players in the 2nd section
4,967,690 ways to choose 4 from 106 in the 3rd section
36 ways to choose 1 from 36 in the 4th section
1,215,450 ways to choose 4 from 75 in the 5th section
3,183,545 ways to choose 4 from 95 in the 6th section.
That makes a total of 3,683,756,501,915,800,000,000,000,000 combinations -- 3.6 billion billion billion. There hasn't been enough paper made since time began to print them all.
Or maybe I'm totally misunderstanding what you want?
what i ment was the numbers cannot be repeated more than once per combination. since they represent names, 2 of the same person is not possible per combination.Quote:
but no numbers can be repeated in their respective columns, so in the 1st column for example say the number 2 is in it. when it picks 3 numbers from that column, it can only pick number 2 once.
i wanted it to print out the actual combination of the numbers not just how many combinations there are.
only use the 1st 5 names of each list to make things easier.
e.g one comination i want printed out would be:
1000
1001
1002(3 from 1st)
1098(1 from second)
1133
1134
1135
1136(4 from 3rd)
1239(1 from 4th)
1275
1276
1277
1278(4 from 5th)
1350
1351
1352
1353(4 from 6th)
thanks for the help.
OK, I understand.what i ment was the numbers cannot be repeated more than once per combination.
That was my earlier point. There are too many (way, way too many) to print them all.i wanted it to print out the actual combination of the numbers not just how many combinations there are.
what if you use only the 1st 5 IDs of each column though? how would this be done?
That's a much more manageable number -- just fits on a single worksheet. Drag the formulas down to combination 62,500.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks