Select a Random Number within a group and make that selected number unique among groups

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)
Problem_partial_solution.png

I need to insert more column/rows and use this formula.

Please Login or Register  to view this content.

By the way, it's did not a final result, you may need to re-calc more than one time until got the goal.

Regards.

Originally Posted by Gregb11

I think the issue is, based on your rules, you could have data that can't be solved. If you 9 groups and they all have 1,2,3,4 as their 4 numbers (or some other small variations), you won't be able to make all random numbers unique.

Thank you for responding... The groups do not have 1,2,3,4 They will be numbers between 1-9 but only 4 numbers from that set for that group.
The numbers actually could be names... the randbetween formula selects which of the four will be placed in the cell below the group list.
What I am looking for I guess is that I need go through the group result and then compare to the already found group result to see if there is
a duplicate result. If so then take a look at the remaining group items to see if I can place another number that would be unique from the group's list. THen go to the next group and do the same iteration. The resulting row would be numbers 1 - 9 placed in the row each number being used in the row uniquely.

So maybe I need a help Column or Row to keep track what numbers have already been used...

Hi,

See if this result fits your problem. You really don't need the first two steps as all is random. Click the button and get instant answers.

Rand Result Backwards.xlsm

Hi Chey,

You pick 4 random numbers from 9 and then one random number from 4 of the first pick. You end up wanting a full set of the random numbers. My contention is that you don't need the first two steps. Yep - you heard me right! Make the final answer and work backwards. You asked this question wanting an answer. This is mine.

Step back and ask yourself, if you pick random numbers 3 times, does it make it more random than picking them once? By putting constraints on your random choices, do they become more or less random? Perhaps this problem is to show you this dilemma.

Try this

Formula:

Please Login or Register  to view this content.

It only works because of the distribution of the numbers throughout the sample. With any other combination I doubt it will work. It's a bit of a "cheat" alternating Min/Max numbers in the resulting arrays. The 9th column caused me fits. All the numbers were already used and all that was needed was a 1. There are no 1s in the 9th column.

	B	C	D	E	F	G	H	I	J
2	A	B	C	D	E	F	G	H	I
3	6	6	6	1	2	1	1	9	4
4	7	3	9	7	3	9	5	4	8
5	5	4	5	5	8	8	4	2	2
6	8	7	7	9	9	2	3	6	3
7
8	7	7	5	1	3	8	4	2	4
9
10	5	7	6	9	2	8	1	4	3

This formula is a little shorter. Same issues, same results.

Formula:

Please Login or Register  to view this content.

Hey Gregb,

I agree that many times we get problems that have no solution or the method is "obscure". My point is that 3 levels of random numbers are just as random as one level of random numbers. I believe Dave found there is no solution after going through Chey's "method". If you need to change the random numbers to get from step 1 to 2, so they can get to step 3, why didn't he simply start with the final answer. My question is: Are 3 levels of random numbers better than one? Isn't that the point of "Random" numbers?