Even distribution of task

[bananaboy]

Hi! I work as a manager in a hospital. I have 18 employees. I would like to automatically evenly distribute certain tasks to them in a weekly roster (mon-fri). All task are performed every day. Task K1, K2 and K3 demands two people each every day. Task B demands three people every day. Ideally, the same person shouldnt be doing the same task several days in a row. Help please?

 

[maabadi]

Welcome to MrExcel Message Board.
Then You need only 9 person per day for all works. Is this Correct?

 

[bananaboy]

Welcome to MrExcel Message Board.
Then You need only 9 person per day for all works. Is this Correct?

Hi! I see that I forgot one task, lets call it SCR, and I need three people for that as well. So that would make it 12 person pr day for our main tasks, as some have individual task that vary from day to day.

 

[maabadi]

How about:
after create copy data and paste them at another cells as Values only.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Weekdays
2		Tasks	Monday	Tuesday	Wednesday	Thursday	Friday		Create Random Numbbers
3		K1	Employee 13	Employee 10	Employee 15	Employee 12	Employee 9		25362	58204	34077	43588	70523	30169
4		K1	Employee 16	Employee 8	Employee 8	Employee 2	Employee 18		16946	63291	65474	85749	7439	91712
5		K2	Employee 5	Employee 12	Employee 16	Employee 10	Employee 15		63233	34189	25946	45564	27133	39113
6		K2	Employee 14	Employee 11	Employee 4	Employee 3	Employee 14		24916	51508	83736	83398	30309	7652
7		K3	Employee 7	Employee 4	Employee 9	Employee 13	Employee 5		61060	74413	55386	39772	83592	4483
8		K3	Employee 8	Employee 16	Employee 10	Employee 1	Employee 8		58171	12849	50795	89433	80264	70214
9		B	Employee 11	Employee 18	Employee 13	Employee 9	Employee 12		51664	462	46602	48981	54969	93769
10		B	Employee 12	Employee 9	Employee 7	Employee 18	Employee 16		34841	62767	66575	15205	26898	13799
11		B	Employee 18	Employee 6	Employee 14	Employee 5	Employee 1		4481	69313	35785	71004	99557	11089
12		SCR	Employee 2	Employee 2	Employee 5	Employee 11	Employee 2		91977	85175	71561	44831	91514	57094
13		SCR	Employee 4	Employee 7	Employee 2	Employee 8	Employee 10		65468	65472	88593	55364	59416	44195
14		SCR	Employee 6	Employee 14	Employee 17	Employee 4	Employee 11		61919	22722	11485	75277	55620	27834
15		D1	Employee 15	Employee 15	Employee 12	Employee 17	Employee 17		22417	15588	47735	20663	8845	77533
16		D2	Employee 9	Employee 1	Employee 11	Employee 15	Employee 7		56099	89559	49129	37529	80729	1916
17		D3	Employee 17	Employee 5	Employee 3	Employee 6	Employee 4		10584	72269	84942	62688	85920	36427
18		D4	Employee 1	Employee 3	Employee 18	Employee 16	Employee 3		98888	75644	4325	30668	86590	59654
19		D5	Employee 3	Employee 13	Employee 1	Employee 7	Employee 6		73729	33769	90054	57330	83210	84823
20		D6	Employee 10	Employee 17	Employee 6	Employee 14	Employee 13		53833	1359	70239	38464	48558	61885
21
Sheet1

Cell Formulas
Range	Formula
C3:G20	C3	="Employee " & RANK.AVG(I3,I$3:I$20)
I3:N20	I3	=RANDBETWEEN(1,100000)

 

[bananaboy]

Thank you, I will try this! I am a total amateur though. Any easy solution to put in their actual names insted of "employee"?

 

[maabadi]

Change employee Name to what you want.
Don't forgot, After create name Copy them and then Paste values at another Sheet. Because of using Rand function name changing each time press ENTER key.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					Weekdays
2	No.	Name		Tasks	Monday	Tuesday	Wednesday	Thursday	Friday		Create Random Numbbers
3	1	Mike		K1	Garcia	Michel	Eric	Peter	Phil		27558	59395	22377	52149	76071	64506
4	2	Emily		K1	Phil	Sara	Garcia	Phil	July		73843	80695	20510	68695	6398	51512
5	3	Harry		K2	Cathrin	Andy	Donald	Andy	Sara		72432	26425	34335	26851	72961	91022
6	4	Phil		K2	Michel	John	Mike	John	Emily		38026	62092	94318	49598	95529	97615
7	5	Sara		K3	July	Phil	Kendall	Harry	Eric		4931	86387	66627	68801	30868	48478
8	6	Cathrin		K3	Kendall	Rudy	Phil	Susan	Garcia		70975	29831	88461	51632	29237	51137
9	7	Kendall		B	Donald	Emily	Michel	July	John		37127	88128	37404	15868	49695	97622
10	8	Peter		B	Harry	Mike	Sara	Kendall	Cathrin		73975	98885	88457	56650	62833	16257
11	9	Susan		B	Susan	Susan	Andy	Mike	Kendall		47104	65049	9712	92168	62716	68157
12	10	John		SCR	Mary	Peter	Emily	Rudy	Rudy		22043	67973	94250	31251	21612	82878
13	11	Michel		SCR	Peter	Donald	Peter	Emily	Donald		66365	48916	53847	76769	30953	73794
14	12	Donald		SCR	Rudy	Cathrin	Mary	Mary	Harry		15590	73443	18790	32169	88909	44219
15	13	Eric		D1	Andy	Harry	Rudy	Sara	Susan		10340	87411	10816	65594	51634	18806
16	14	Garcia		D2	Eric	July	Cathrin	Garcia	Mary		34406	17662	69037	33615	26353	59106
17	15	Mary		D3	John	Mary	John	Michel	Peter		43324	37270	40507	48835	58964	39725
18	16	Rudy		D4	Sara	Garcia	Susan	Cathrin	Andy		73366	40888	46532	58548	7368	95208
19	17	Andy		D5	Emily	Eric	July	Donald	Mike		82959	45164	502	35431	95606	57644
20	18	July		D6	Mike	Kendall	Harry	Eric	Michel		98518	68856	93078	34925	47518	69628
21
Sheet1

Cell Formulas
Range	Formula
E3:I20	E3	=VLOOKUP(RANK.AVG(K3,K$3:K$20),$A$3:$B$20,2,FALSE)
K3:P20	K3	=RANDBETWEEN(1,100000)

 

[bananaboy]

That worked brilliantly, thank you so much!

 

[maabadi]

You're Welcome & Thanks for follow-up.