Spreadsheet for random names in a set period of time (for drug testing).

[jchebert1979]

Hello everyone, this is my first post after watching a couple videos on Mr. Excel's blog. I only have a minimal amount of knowledge with excel's formulas, and I am a helping a small doctor's office that just implemented drug testing. Right now the doctor is doing it every month and agrees that it is overkill for both her and the patients, but also if the patient knows they will be tested every month, they can potentially beat it. I've looked into paid programs that can do what they need, but they are just too expensive for the clinic.

I came across a Mr Excel blog post from 2015 in which the sheet gives you random names using if(is) and (rand) along with VLookup for the names, however it does not account for a set period of time. There are also a few other videos on YouTube that use different formulas, but the result is very similar. I need a formula that will give names to test every patient randomly, two times in a six month period. Can this even be done and if so can anyone help me?

Thanks, Jason

 

[AlanY]

see if you can develop something from this

Patients are in A3 to A32, their previously test dates in B3 to F32 (created randomly for testing)
Col G to work out number of tests within last 6 months prior.
Col H to create a random number for each patient
Cell A1 to pick random patient but excl those has 2 or more tests within last 6 months (<=2 in Col G)

Press F9 to recalculate thus pick another patient

Book1
	A	B	C	D	E	F	G	H
1	Patient 5
2		Test date
3	Patient 1	16/01/2019	26/01/2019	25/04/2019			0	0.26070383
4	Patient 2	31/10/2019	04/07/2019	29/05/2019			1	0.832238414
5	Patient 3	14/02/2019	17/02/2019	15/03/2019			0	0.91730194
6	Patient 4	05/03/2019	16/03/2019	01/08/2019			1	0.726770643
7	Patient 5	04/02/2019	06/02/2019	15/07/2019			0	0.07249397
8	Patient 6	23/03/2019	26/11/2019	30/03/2019			1	0.082798858
9	Patient 7	20/12/2019	30/10/2019	12/10/2019			3	0.114130873
10	Patient 8	01/11/2019	03/11/2019	22/12/2019			3	0.220755477
11	Patient 9	04/02/2019	26/01/2019	10/07/2019			0	0.43424456
12	Patient 10	07/01/2020	17/09/2019	25/11/2019			3	0.496984819
13	Patient 11	22/01/2019	05/04/2019	08/06/2019			0	0.916884105
14	Patient 12	08/10/2019	13/06/2019	22/01/2019			1	0.132690021
15	Patient 13	24/05/2019	25/01/2019	25/12/2019			1	0.640062885
16	Patient 14	13/07/2019	26/03/2019	04/08/2019			1	0.273768115
17	Patient 15	03/08/2019	06/06/2019	12/12/2019			2	0.034328888
18	Patient 16	16/07/2019	18/08/2019	07/12/2019			2	0.371134669
19	Patient 17	15/06/2019	29/12/2019	29/03/2019			1	0.353927845
20	Patient 18	27/07/2019	21/04/2019	09/02/2019			1	0.425306048
21	Patient 19	05/01/2020	03/04/2019	27/12/2019			2	0.881991127
22	Patient 20	19/04/2019	26/04/2019	30/11/2019			1	0.574746728
23	Patient 21	18/02/2019	08/06/2019	05/01/2020			1	0.202832651
24	Patient 22	06/10/2019	08/03/2019	30/04/2019			1	0.313755041
25	Patient 23	03/09/2019	05/02/2019	18/12/2019			2	0.899181462
26	Patient 24	12/02/2019	18/06/2019	31/07/2019			1	0.849615504
27	Patient 25	07/03/2019	02/07/2019	10/11/2019			1	0.533057934
28	Patient 26	25/02/2019	05/12/2019	18/01/2019			1	0.955715317
29	Patient 27	15/08/2019	07/11/2019	26/09/2019			3	0.442405024
30	Patient 28	01/09/2019	19/08/2019	12/07/2019			2	0.772979177
31	Patient 29	14/01/2020	10/09/2019	18/08/2019			3	0.347549786
32	Patient 30	15/05/2019	04/10/2019	21/12/2019			2	0.977656146
Sheet5

Cell Formulas
Range	Formula
A1	A1	=INDEX(A3:A32,MATCH(MINIFS(H3:H32,G3:G32,"<"&2),H3:H32,0))
B3:D32	B3	=TODAY()-RANDBETWEEN(1,365)
G3:G32	G3	=COUNTIF(B3:F3,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))
H3:H32	H3	=RAND()

click this symbol to copy the spreadsheet

 

[jchebert1979]

Wow, thank you so much, Alan. I'm sorry for the late response. I had some health problems, ended up in the hospital, and was feeling too bad to even look at the computer. Again, I appreciate it. I will let you know how it goes when I start to test it out.

 

[jchebert1979]

The only problem I'm having is cell A1 is not generating a patient name. I tried a few different things, but I just ended up messing thing up.

 

[AlanY]

sorry to hear about you're not being well, hope you're better now.

have you tried the below method to copy the excel file for testing?

 

[jchebert1979]

Yes, that's what I used. I've gone through the formulas as well and compared them to the formula sheet, they all look correct. I just tried setting hard dates instead of randomly generated ones. Now I'm trying to learn what the formulas mean and do.

 

[AlanY]

ok, so what is it showing on A1?

 

[AlanY]

could you post the file that's not working like I did in post#2 using

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[jchebert1979]

Cell A1 is showing #Name?

I'm using your link right now.

 

[AlanY]

right, your excel version doesn't has the MINIF() function, try this instead

Book1
	A	B	C	D	E	F	G	H	I
1	Patient 23
2		Test date
3	Patient 1	05/04/2019	24/09/2019	01/06/2019			1	0.705166266
4	Patient 2	24/08/2019	03/04/2019	27/04/2019			1	0.114845709
5	Patient 3	16/08/2019	13/08/2019	17/01/2020			3	0.358795684
6	Patient 4	17/10/2019	26/11/2019	25/01/2019			2	0.835985197
7	Patient 5	02/01/2020	17/09/2019	07/11/2019			3	0.370956742
8	Patient 6	31/01/2019	25/02/2019	20/05/2019			0	0.230235661
9	Patient 7	12/01/2020	16/12/2019	03/06/2019			2	0.738026772
10	Patient 8	02/08/2019	17/06/2019	27/01/2019			1	0.742011846
11	Patient 9	16/04/2019	07/09/2019	14/03/2019			1	0.392700375
12	Patient 10	28/10/2019	04/08/2019	02/04/2019			2	0.342311803
13	Patient 11	30/05/2019	10/01/2020	19/09/2019			2	0.263064444
14	Patient 12	05/02/2019	06/03/2019	02/04/2019			0	0.357876161
15	Patient 13	13/10/2019	20/08/2019	22/11/2019			3	0.738432299
16	Patient 14	20/08/2019	20/02/2019	29/06/2019			1	0.385405905
17	Patient 15	19/09/2019	27/11/2019	30/11/2019			3	0.01071849
18	Patient 16	16/08/2019	05/05/2019	14/02/2019			1	0.949761439
19	Patient 17	01/08/2019	19/03/2019	27/06/2019			1	0.370048489
20	Patient 18	07/10/2019	05/12/2019	01/03/2019			2	0.105753096
21	Patient 19	20/12/2019	19/10/2019	05/10/2019			3	0.065534724
22	Patient 20	05/10/2019	16/11/2019	15/08/2019			3	0.228139342
23	Patient 21	14/05/2019	11/03/2019	27/03/2019			0	0.85646768
24	Patient 22	07/12/2019	12/07/2019	27/04/2019			1	0.807335585
25	Patient 23	28/03/2019	25/06/2019	23/08/2019			1	0.094144026
26	Patient 24	26/01/2019	14/11/2019	27/05/2019			1	0.828634287
27	Patient 25	10/08/2019	18/11/2019	01/10/2019			3	0.452773207
28	Patient 26	19/04/2019	26/10/2019	10/10/2019			2	0.874112405
29	Patient 27	26/03/2019	13/01/2020	24/05/2019			1	0.638615103
30	Patient 28	17/12/2019	09/07/2019	30/10/2019			2	0.935714096
31	Patient 29	07/10/2019	13/05/2019	22/01/2019			1	0.197396617
32	Patient 30	07/03/2019	27/06/2019	02/07/2019			0	0.818142974
33
Sheet4

Cell Formulas
Range	Formula
A1	A1	{=INDEX(A3:A32,MATCH(MIN(IF(G3:G32<2,H3:H32)),H3:H32,0))}
B3:D32	B3	=TODAY()-RANDBETWEEN(1,365)
G3:G32	G3	=COUNTIF(B3:F3,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))
H3:H32	H3	=RAND()
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.