Select a random number from a list then remove, rinse, repeat

[tezza]

Hi all,

Ok, I'll try to explain what I need.

I have a number list 1 - 90

The numbers are split in 9 groups

I need to randomly pick 5 groups then randomly pick 1 number from each of the groups,
Show the numbers picked
remove the picked numbers from the original list

After the first round of picks the list of numbers reduce by five

With the remaining numbers I need to repeat the process - choose 5 random numbers from 5 random groups and remove from list

Rinse and repeat until there are no numbers left

When all the numbers in a group have been used then that group needs to be eliminated.

Bingo.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR
1	Random No:						Group									Group										Group										Group										Group										Group										Group										Group										Group
2	1	2	3	4	5		1									2										3										4										5										6										7										8										9
3	11	34	40	68	71		1	2	3	4	5	6	7		9	10		12	13	14	15	16	17	18	19	20		22	23	24	25	26	27	28	29	30	31	32	33		35	36	37	38			41	42	43	44		46	47		49	50	51	52	53	54		56		58	59	60	61		63	64	65	66	67		69	70		72	73	74	75	76	77	78	79		81	82	83	84		86	87	88	89	90
4	8	21	48	55	80		1	2	3	4	5	6	7		9	10		12	13	14	15	16	17	18	19	20		22	23	24	25	26	27	28	29	30	31	32	33		35	36	37	38			41	42	43	44		46	47		49	50	51	52	53	54		56		58	59	60	61		63	64	65	66	67		69	70		72	73	74	75	76	77	78	79		81	82	83	84		86	87	88	89	90
5	39	45	57	62	85		1	2	3	4	5	6	7		9	10		12	13	14	15	16	17	18	19	20		22	23	24	25	26	27	28	29	30	31	32	33		35	36	37	38			41	42	43	44		46	47		49	50	51	52	53	54		56		58	59	60	61		63	64	65	66	67		69	70		72	73	74	75	76	77	78	79		81	82	83	84		86	87	88	89	90
Sheet1

 

[Legacy 143009]

It would be easier if grouping like this is possible:
1-10, 11-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80, 81-90

Will it be a big deal?

 

[tezza]

It would be easier if grouping like this is possible:
1-10, 11-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80, 81-90

Will it be a big deal?

Hi, no it has to be like in my example otherwise it won't work.

 

[tezza]

If it helps, I only need to see the five numbers randomly chosen, the rest is just a visual for posting on here.

 

[Peter_SSs]

otherwise it won't work.

a. Why not? What is the logic/theory behind that assertion?

b. It mostly won't work anyway. If the choices are truly random, it is quite likely that when you get down to the last 5 numbers (or even before that) the remaining numbers will be in 4 or less groups so it is then not possible to choose a number from 5 different groups.

 

[tezza]

a. Why not? What is the logic/theory behind that assertion?

b. It mostly won't work anyway. If the choices are truly random, it is quite likely that when you get down to the last 5 numbers (or even before that) the remaining numbers will be in 4 or less groups so it is then not possible to choose a number from 5 different groups.

No, if done right then the last group of 5 random numbers will be in 5 different groups which will then finish the search.

It reduces by 5 numbers EVERY time from 5 different group. If say group one is empty then it needs to look in a different group that contains at least one number.

This is what I'm after (the grid on the right), or the first three lines of it at least.

The left grid is just a visual for here.

So, 5 random numbers from 5 random groups (1-9), if a group is empty then move a one that isn't.

Hope that helps explain it a little better.

 

[tezza]

Bad example sorry, there's meant to be 5 numbers in a row, not 4 like in row 3 (rushed it)

 

[Peter_SSs]

I don't think that you are fully understanding random choices.
If the 5 groups are chosen at random then it is possible that for the first 10 passes, groups 2,4,5,7 & 8 could be chosen every time. That would leave those groups empty and the remaining groups would be groups 1, 3, 6 and 9. That is, 4 groups only.

Whilst the above is an extremely remote possibility, the concept is real. Here is an example where I have actually done what you requested. I started with numbers 1 to 90 in G14:CR14, chose 5 random groups, chose a random number out of each of the 5 groups and removed them. Repeated the process and this is where I ended. How can I now choose 5 different groups for my next turn?

tezza.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR
12	Group									Group										Group										Group										Group										Group										Group										Group										Group
13	1									2										3										4										5										6										7										8										9
14	1													14																																	47																																	80							87
90

 

[tezza]

I don't think that you are fully understanding random choices.
If the 5 groups are chosen at random then it is possible that for the first 10 passes, groups 2,4,5,7 & 8 could be chosen every time. That would leave those groups empty and the remaining groups would be groups 1, 3, 6 and 9. That is, 4 groups only.

Whilst the above is an extremely remote possibility, the concept is real. Here is an example where I have actually done what you requested. I started with numbers 1 to 90 in G14:CR14, chose 5 random groups, chose a random number out of each of the 5 groups and removed them. Repeated the process and this is where I ended. How can I now choose 5 different groups for my next turn?

tezza.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR
12	Group									Group										Group										Group										Group										Group										Group										Group										Group
13	1									2										3										4										5										6										7										8										9
14	1													14																																	47																																	80							87
90

You have two numbers from group 9, where there should only be one

Think of it like making a rand() list of 9 numbers then rank them in order and choose the first five numbers

That will be your five groups (the problem is when the group is empty so you either have to run it again or tell it to ignore the empty group)

Once you have your 5 groups then pick a random number within the group.

 

[tezza]

Here's an example of what should be left after each line only allowing 5 numbers

The last group of 5 numbers will slot into the rows where the count is only 4.

Now I just need to automate it (again, only the right grid is visually needed but if the left grid is needed to make it work then I can simply hide it)