1) 9 groups of 4 numbers (integers)
2) Each integer value less that 10
3) Each number is unique within the group
4) Multiple Groups can have same number(s) Group A 1,3,7,5 - Group B 2, 5, 8, 9- etc.
Want to randomly select a number within group of 4
But each group must have a unique resulting number between the groups.
(i.e. no group can have the same resulting number)
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How can I select a Random Number within a group and make that selected number unique among other groups?
0
Discussion
Since a number may be repeated in another group it is theoretically possible that a particular number occurs in each of the 9 groups. In that case it would be impossible to avoid its repetition in the series of 9 random numbers. In fact, I think its recurrence in 4 groups might be sufficient to create that scenario. Anyway, since there isn't a logical solution to your task based on the rules you mention it should be impossible to create a formula that solves the problem.
Variatus (rep: 4889) Nov 19, '19 at 5:31 am
Thank you for responding. Yes it will most likely have duplicate numbers among the 9 groups. I was trying to find a solution that I could check each group and assign a number for that group that could not be duplicated by the other group. I was thinking I may need a help table to retire that number if it has been used already. then go about checking the next group and eliminate that retired number as a possiblity.
Cheyennemtnman Nov 19, '19 at 5:38 pm
Answers
0
Please construct theoretical situations in which ...
- all of the 4 numbers in a group have already been selected as exponent of another group, and
- where it might be impossible to make any of the 4 choices in a group unique among the exponents even by changing the selected exponents of other groups.
Since neither of these two scenarios can be excluded under your current rules you will have to change the rules before your idea can be implemented.
I think the second scenario can be avoided by disallowing the recurrence of any number in the 9 groups for more than 3 times. That seems like a tough requirement because statistically the 36 numbers in all 9 groups require each number between 1 and 9 be included at least 4 times. Therefore it should be impossible to avoid the first scenario, either.
The exponents drawn from the 9 groups are, in fact, a list of the 9 numbers which are smaller than 10. It will be impossible to extract such a list from the 9 groups unless every number from 1 to 9 is, in fact, included in at least one of the groups. Since the groups of 4 are created at random it would be possible that none of the 9 groups include, say, the number 3.
If you manage to create 9 groups, made up of all the numbers between 1 and 9 and none of them selected more than thrice (statistically impossible task) your system would create a random lineup of the numbers 1 to 9. If that is what you need, couldn't you create it more efficiently by just creating a random sequence?
I point out that the creation of the 9 selection groups has so many rules that very little is actually left to chance. It will not be possible to claim that the groups were assempled at random. With that said, if you need the groups to have a specific relationship with the lineup, can you consider designing a system by which the groups are created from the lineup instead of the other way around? You will probably be able to allow more randomness that way.