Random Number

[Iccreamann21]

I'm looking for a formula that will pick a random number, but consider using the number used the least.

Names:
30ish enteries

Weeks:
52 weeks

Numbers:
1-100

The goal is to use all 100 numbers before recycling them. Thank you, your help is much appreciated!

names	week 1	week 2	week 3	week 4
mike	88	34	2	33
bill	16	78	5	58
dave	23	63	27	8
matt	76	85	24	21

 

[Iccreamann21]

 

[Snakehips]

@Iccreamann21
I think you can infer from the lack of response that this is not as easy as it might sound.
Certainly not by way of a formula.
One thing to bear in mind is that the randomising formulas are 'Volatile' and, as such, they re-calculate every time your sheet calculates. So direct use in formulas would likely yield ever changing results.
I would imagine that you are wanting the random picks to stay stable?
I have half a notion of how you might use formulas to generate your random numbers but it would then require you to copy / paste the generated values to your data columns.
No promises but if something along those lines is acceptable to you then I will see if I can actually achieve it.
Otherwise, you will need to employ a vba Macro.
Is this up to 30 numbers generated each week or a full year of weeks generated as a one off?
Is 30 a realistic maximum ?

 

[Iccreamann21]

@Iccreamann21
I think you can infer from the lack of response that this is not as easy as it might sound.
Certainly not by way of a formula.
One thing to bear in mind is that the randomising formulas are 'Volatile' and, as such, they re-calculate every time your sheet calculates. So direct use in formulas would likely yield ever changing results.
I would imagine that you are wanting the random picks to stay stable?
I have half a notion of how you might use formulas to generate your random numbers but it would then require you to copy / paste the generated values to your data columns.
No promises but if something along those lines is acceptable to you then I will see if I can actually achieve it.
Otherwise, you will need to employ a vba Macro.
Is this up to 30 numbers generated each week or a full year of weeks generated as a one off?
Is 30 a realistic maximum ?

Snakehips,

Thank you for the reply. I kind of figured this would be the case, but I know we have some strong outside-of-the-box members on the forums such as yourself.

You are correct, I would need the numbers to stay stable for record purposes. If your suggestion of copy/paste generated values is achievable, I would be greatly appreciative. Unfortunately for me, due to security reasons, VBA macro is a security risk and is blocked from computers on the network.

The exact number of employees is 50 and each week they would receive a number between 1-95. The numbers would need to be generated weekly and each number would have to be distributed before they are able to be recycled and used again.

Let me know your thoughts and I most definitely appreciate you taking your time to help me with this task.

 

[Snakehips]

@Iccreamann21 Ok, leave me to have a think and a play,

Would you want the 'Random Generator' on a separate sheet or same sheet as the record of picks?

 

[Iccreamann21]

@Iccreamann21 Ok, leave me to have a think and a play,

Would you want the 'Random Generator' on a separate sheet or same sheet as the record of picks?

A separate sheet would be best. Thanks again

 

[Snakehips]

Ok. Another thought / question.
Will the list of 50 employees always be full?
Will there be some weeks when certain employees might be excluded from attracting a random number?

 

[Iccreamann21]

Ok. Another thought / question.
Will the list of 50 employees always be full?
Will there be some weeks when certain employees might be excluded from attracting a random number?

Most definitely!

 

[Snakehips]

@Iccreamann21 Where to begin ? Other than I must want my bumps read, having wrestled with this!!!!

Below is my best effort which hopefully will do what you want.
It has two elements of randomisation and should ensure that each batch of numbers 1 - 95 is fully used before introducing a new batch.
I have tested it but it now needs someone with a less frazzled brain to take a calm look.

The 'Random Picks' tab:
Can be renamed if you wish.
Has a Named Range ‘Picks’ - which is 52 Week columns x 50 Employee rows.
Column A. Set ‘Y’ to include or ’N’ to exclude employee(s) from the current pick.
Clear Contents of week column(s) to do re-pick.

The ‘Generator’ tab:
This tab is perhaps best kept protected. Currently no password.
Helper columns A :N can be hidden if required.
N reflects the inclusion choices made in column A of the Random Picks tab.
To generate a new set of picks just force calculation by hitting F9 key.
Then Copy the green range P2:P51, inclusive of any blanks, and Paste Special/Values into the appropriate column of the Random Picks tab.
Immediately you do that, the Generator will re-calculate and so you could Paste new values into next week column if you wish.
When done, double click on any cell to cancel Cut/Copy mode which will stop the marching ants border in Generator tab.
You can can double click in the Generator tab but that will need you to dismiss a pop up protection Alert.

I think that’s about it?
I hope the following XL2BB Mini Sheets reflect everything.
In the Generator tab, drag the formulas in A55:B55. and D55:F55 down to row 96.

RandomXl.xlsx
	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
1	Include	Employee	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52
2	Y	Mike	43	78	3	81	22	85	80	82	89	58
3	Y	Bill	57	68	37	39	55	63	57	84	39	75
4	N	Dave	55	93	46	82	76	36	79	87	22
5	N	Matt	58	41	52	2	27	57	15	81	57
6	Y	Emp6	24	8	38	73	86	74	12	16	86	69
7	Y	Emp7	50	14	40	85	11	21	63	54	4	60
8	Y	Emp8	36	91	31	28	59	4	4	75	49	91
9	Y	Emp9	7	86	27	66	31	56	85	22	90	44
10	Y	Emp10	48	1	72	41	41	8	90	74	58	3
11	N	Emp11	40	80	44	43	30	94	45	17	70
12	Y	Emp12	21	64	1	95	29	19	21	42	62	42
13	Y	Emp13	92	39	51	94	15	61	66	14	40	24
14	Y	Emp14	45	66	30	13	88	64	46	25	31	5
15	Y	Emp15	77	60	78	75	68	79	70	34	32	11
16	Y	Emp16	3	32	25	48	13	58	86	26	1	9
17	Y	Emp17	19	61	15	93	49	14	61	27	25	65
18	Y	Emp18	85	52	36	4	2	78	48	58	7	8
19	Y	Emp19	44	84	89	68	83	43	53	93	85	10
20	Y	Emp20	29	37	8	67	23	42	52	36	82	26
21	Y	Emp21	53	17	24	6	65	12	18	29	20	66
22	Y	Emp22	81	75	59	33	89	5	71	32	83	14
23	Y	Emp23	15	94	11	60	54	24	95	77	81	43
24	Y	Emp24	33	20	50	7	38	81	73	49	88	72
25	Y	Emp25	83	74	80	92	60	53	89	1	28	46
26	Y	Emp26	5	59	20	34	75	37	2	6	14	39
27	Y	Emp27	88	9	35	58	73	25	50	5	95	27
28	Y	Emp28	70	30	16	77	35	66	64	43	79	86
29	Y	Emp29	76	63	23	53	39	28	13	23	69	87
30	Y	Emp30	18	2	71	42	6	34	40	62	41	67
31	Y	Emp31	89	38	69	90	87	72	8	69	74	47
32	Y	Emp32	23	46	14	54	40	9	37	12	17	29
33	Y	Emp33	4	62	74	86	16	45	24	48	45	52
34	Y	Emp34	56	95	91	47	77	95	56	18	6	64
35	Y	Emp35	73	26	32	63	26	48	60	60	51	77
36	Y	Emp36	49	22	62	12	47	93	72	63	34	13
37	Y	Emp37	34	27	84	87	82	51	44	15	53	90
38	Y	Emp38	10	35	5	56	84	11	19	55	68	36
39	Y	Emp39	87	42	64	9	7	39	94	33	54	56
40	Y	Emp40	13	28	45	26	3	20	28	37	35	15
41	Y	Emp41	79	47	18	61	17	41	83	21	36	61
42	Y	Emp42	72	12	19	1	20	33	9	2	26	93
43	Y	Emp43	90	54	17	50	67	68	38	30	73	71
44	Y	Emp44	51	82	76	71	90	59	88	94	16	35
45	Y	Emp45	65	25	22	51	33	76	3	19	27	38
46	Y	Emp46	16	6	21	46	69	10	7	24	92	51
47	Y	Emp47	67	49	79	80	91	35	78	46	91	4
48	Y	Emp48	69	83	29	18	92	67	91	59	23	50
49	Y	Emp49	11	70	10	70	62	65	31	67	76	83
50	Y	Emp50	71	88	55	10	52	55	47	44	84	79
51	Y	Charlie	31	57	65	32	44	92	30	78	80	89
Random Picks

Cells with Data Validation
Cell	Allow	Criteria
A2:A51	List	Y,N

RandomXl.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Random Root Number	Random Rank 1 - 95	Priority Pick Ref	Prioritiy Pick	Priority Number Index	Top-up Number Index	Priority Pick	Top-up Index	All Picks	Random For Distribution	Distribution Rank	Distribution Index		Employee Included		Copy Paste Special/Values
2	0.390060338	66	5	TRUE	1	999	66	-	66	0.738383932	35	1		TRUE		19
3	0.369650208	68		TRUE	2	999	68	-	68	0.746208527	36	2		TRUE		57
4	0.961029659	7		TRUE	3	999		-		999	48			FALSE
5	0.386278388	67		FALSE	999	4		-		999	48			FALSE
6	0.295773616	74		TRUE	5	999	7	-	7	0.724352081	34	3		TRUE		54
7	0.489329205	54		TRUE	6	999	74	-	74	0.84071085	41	4		TRUE		59
8	0.168992393	88		TRUE	7	999	54	-	54	0.033005029	3	5		TRUE		56
9	0.790464742	22		TRUE	8	999	88	-	88	0.589940017	23	6		TRUE		93
10	0.061573791	92		TRUE	9	999	22	-	22	0.602260493	25	7		TRUE		94
11	0.262990508	79		FALSE	999	10		-		999	48			FALSE
12	0.743066501	31		TRUE	11	999	92	-	92	0.644786738	30	8		TRUE		33
13	0.639332303	40		TRUE	12	999	31	-	31	0.7846525	37	9		TRUE		34
14	0.177077548	87		TRUE	13	999	40	-	40	0.346986223	13	10		TRUE		72
15	0.753573007	29		TRUE	14	999	87	-	87	0.829728199	39	11		TRUE		71
16	0.852621964	14		FALSE	999	15	29	-	29	0.895628147	44	12		TRUE		9
17	0.465551078	57		TRUE	16	999	57	-	57	0.016852687	2	13		TRUE		40
18	0.712257183	34		TRUE	17	999	34	-	34	0.201245242	9	14		TRUE		95
19	0.26852349	78		TRUE	18	999	78	-	78	0.570772216	21	15		TRUE		76
20	0.894349497	10		TRUE	19	999	10	-	10	0.844156045	42	16		TRUE		13
21	0.775934061	26		FALSE	999	20	21	-	21	0.636463761	28	17		TRUE		80
22	0.793105344	21		TRUE	21	999	95	-	95	0.359617918	14	18		TRUE		20
23	0.020666614	95		TRUE	22	999	93	-	93	0.12135144	6	19		TRUE		38
24	0.129823024	90		FALSE	999	23	47	-	47	0.596380731	24	20		TRUE		43
25	0.060738494	93		TRUE	24	999	82	-	82	0.9789706	47	21		TRUE		78
26	0.570153922	47		TRUE	25	999	94	-	94	0.133826455	7	22		TRUE		41
27	0.712078164	36		FALSE	999	26	32	-	32	0.713176028	33	23		TRUE		88
28	0.329858822	69		FALSE	999	27	80	-	80	0.493774018	17	24		TRUE		47
29	0.227564283	82		TRUE	28	999	9	-	9	0.311303388	12	25		TRUE		22
30	0.040667162	94		TRUE	29	999	16	-	16	0.837794818	40	26		TRUE		84
31	0.767724356	27		FALSE	999	30	56	-	56	0.116382289	5	27		TRUE		63
32	0.737116524	32		TRUE	31	999	65	-	65	0.943661108	45	28		TRUE		21
33	0.593823548	46		FALSE	999	32	38	-	38	0.515114474	19	29		TRUE		11
34	0.253261916	80		TRUE	33	999	19	-	19	0.00900903	1	30		TRUE		92
35	0.894429947	9		TRUE	34	999	41	-	41	0.580028045	22	31		TRUE		49
36	0.834163218	16		TRUE	35	999	72	-	72	0.228521323	10	32		TRUE		28
37	0.475416203	56		TRUE	36	999	28	-	28	0.672254587	32	33		TRUE		32
38	0.407251444	65		TRUE	37	999	13	-	13	0.48412208	16	34		TRUE		7
39	0.464556446	58		FALSE	999	38	20	-	20	0.498697168	18	35		TRUE		66
40	0.675450477	38		TRUE	39	999	43	-	43	0.52772589	20	36		TRUE		68
41	0.812214454	19		TRUE	40	999	71	-	71	0.268786148	11	37		TRUE		31
42	0.226664191	83		FALSE	999	41	11	-	11	0.644154805	29	38		TRUE		70
43	0.638276772	41		TRUE	42	999	12	-	12	0.969989289	46	39		TRUE		87
44	0.850107153	15		FALSE	999	43	70	-	70	0.815225894	38	40		TRUE		16
45	0.314176294	72		TRUE	44	999	76	-	76	0.450563297	15	41		TRUE		74
46	0.7676886	28		TRUE	45	999	55	-	55	0.883449529	43	42		TRUE		10
47	0.861218184	13		TRUE	46	999	49	-	49	0.661602523	31	43		TRUE		55
48	0.809625604	20		TRUE	47	999	59	-	59	0.093517211	4	44		TRUE		29
49	0.632630903	43		TRUE	48	999	33	-	33	0.19834459	8	45		TRUE		65
50	0.323542835	71		TRUE	49	999	63	-	63	0.635626745	27	46		TRUE		12
51	0.886786611	11		TRUE	50	999	84	-	84	0.620711204	26	47		TRUE		82
52	0.15596063	89		FALSE	999	51
53	0.882400898	12		TRUE	52	999
54	0.67150984	39		FALSE	999	53
55	0.203348779	86		FALSE	999	54
Generator

Named Ranges
Name	Refers To	Cells
Picks	='Random Picks'!$C$2:$BI$52	D2:D55, C2

 

[Iccreamann21]

@Snakehips You are amazing! Thank you so much. After a long holiday weekend, I returned to work and immediately added your creation to my worksheet and it works like a champion! Thank you again. You are a champion!