Random Selections Query

[ben01635]

Hi,

I have been tasked with creating a tool that will randomly select 'x' amount from a list of entries and distribute to 3 named persons over a 5 day week.

Basically we have 75 servers that each need to be checked once a week (5 days) by any member of a team of 3. Therefore 15 servers per day divided by 3, giving 5 checks per person per day.
I have been able to create a simple random selection in Excel to extract 15 of the 75 listed items, but my problem is I need to ensure that once an entry has been selected, it will not be selected again until the following week.
If possible I would also like to output the data into a second sheet or table that defines the persons name and the day of the week, which could be printed at the end of the week for recording purposes. The following week the whole process would begin again.
I have limited experience with Excel so am not entirely sure what I am proposing is possible. Any information or examples would be gratefully received.

Thanks in advance.... Ben

 

[al_b_cnu]

Hi Ben, welcome to the board!

the simplest method may be as follows:
******** language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>

Book1
	A	B	C	D	E
1	Server	Random	Name 1	Name 2	Name 3
2	Server 1	0.454199	Server 1	Server 26	Server 51
3	Server 2	0.530805	Server 2	Server 27	Server 52
4	Server 3	0.953471	Server 3	Server 28	Server 53
Sheet1

column A contains the server names
column B contains random numbers, formula =Rand()
column C contains Name 1 list - C2 contains the formula =A2, copied down to C26
Column D contains Name 2 list - D2 contains the formula =A27, copied down to D26
column E contains Name 3 list - E2 contains the formula =A52, copied down to E26

Then sort columns A & B using column B as the key to refresh the list.

 

[SteveO59L]

Assuming your list of servers is in a2:a76

Range("b2").Select
ActiveCell.FormulaR1C1 = "=randbetween(0,1000000)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B76"), Type:=xlFillDefault
Range("B2:B75").Select

Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],RC[-1]:R[74]C[-1])"
Range("C3").Select

Range("C2").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],R2C2:R76C2)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C76"), Type:=xlFillDefault
Range("C2:C76").Select

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<16,""Day 1"",IF(RC[-1]<31,""Day 2"",IF(RC[-1]<46,""Day 3"",IF(RC[-1]<61,""Day 4"",""Day 5""))))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D76"), Type:=xlFillDefault
Range("D2:D76").Select

 

[al_b_cnu]

an alternative layout may be:

Sheet1:
columns A = server names, column B = random number

Sheet 2:
******** language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>

Book1
	A	B	C	D	E	F
1		Day 1	Day 2	Day 3	Day 4	Day 5
2	Name 1	Server 41	Server 7	Server 52	Server 28	Server 75
3		Server 39	Server 69	Server 33	Server 32	Server 16
4		Server 44	Server 49	Server 54	Server 35	Server 37
5		Server 68	Server 74	Server 31	Server 2	Server 62
6		Server 50	Server 57	Server 43	Server 59	Server 3
7
8	Name 2	Server 56	Server 61	Server 15	Server 53	Server 10
9		Server 42	Server 26	Server 72	Server 73	Server 23
10		Server 18	Server 63	Server 67	Server 1	Server 70
11		Server 22	Server 46	Server 17	Server 13	Server 36
12		Server 58	Server 30	Server 40	Server 27	Server 5
13
14	Name 3	Server 34	Server 38	Server 45	Server 8	Server 11
15		Server 48	Server 65	Server 6	Server 55	Server 19
16		Server 12	Server 9	Server 51	Server 24	Server 4
17		Server 64	Server 29	Server 60	Server 47	Server 14
18		Server 21	Server 20	Server 71	Server 66	Server 25
Sheet2

 

[ben01635]

Thanks very much al_b_cnu, your second solution was just the ticket!

Steve; wasn't sure if this was a Macro script or something, tried this way and got some peculiar results. I'm sure this is a case of my minimal understanding rather than your code! Thanks for the post though.