Excel generate random number based on array and condition

[carissa7]

Background: I have clients who get drug tested. They need to get drug tested 2x per a 7 day week. however, I don't want them finding out the pattern. So i want the pattern of numbers to change per every week. if someone gets tested monday and wednesday, then the following week they should get tested wednesday and friday.

Excel: As a result in excel I have an array of numbers lets say 1 through 65, to represent the patients. I have a formula that generates random numbers based on the array, for 10 columns. The data is present in 10 columns and 7 rows (sunday through sat (1 week)). Formula here: =RANDARRAY(A2:J8,10,1,65,TRUE). However I need each number / patient to get repeated / tested 2x per every week. I got the formula to generate 7 random numbers per day, based on the array. But what I don't know how to do is make sure the numbers are randomly generated every week with a different pattern.

Let me know if this makes sense.

 

[carissa7]

also there cant be any repeating numbers per column, day.

 

[alansidman]

I would take a different approach. In Column A, your 65 patients or their ID. In Column B, =RANDBETWEEN(1,7). In Column C =RANDBETWEEN(1,7)

Recalculate every week. The number 1 to 7 represent the day of the week. This then is random testing. If Column B and C have per chance the same number for a week, then bump one to the next day.

 

[carissa7]

I would take a different approach. In Column A, your 65 patients or their ID. In Column B, =RANDBETWEEN(1,7). In Column C =RANDBETWEEN(1,7)

Recalculate every week. The number 1 to 7 represent the day of the week. This then is random testing. If Column B and C have per chance the same number for a week, then bump one to the next day.

this works, but how do I make it so that it doesn't generate the same number in the two columns? what does "bump one to the next day mean" like just move it to the next day?

 

[Phuoc]

Try this:

Book1
	A	B	C
1
2		4	5
3		4	6
4		5	4
5		7	1
6		3	6
7		5	7
8		2	7
9		6	3
10		6	1
11		7	1
12		5	4
13		4	7
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=RANDBETWEEN(1,7)
C2:C13	C2	=AGGREGATE(14,6,{1,2,3,4,5,6,7}/({1,2,3,4,5,6,7}<>B2),RANDBETWEEN(1,6))

 

[alansidman]

just move it to the next day?

Yes