Having difficulty making a dynamic skills matrix.

[Zolkora]

Good day folks, as the title suggests I'm attempting to make a dynamic skills matrix, or rather an automated task assigner.

I've been at this for 2 days and I'm at a loose end. I don't even know if it's possible with formula in Excel. Let me explain what's going on.
So across the top we have the duties, some are numbered and some are named. Down the left obviously are the employees names.
In the middle is a score from 1 to 10 on how each employee performs on each duty. (ie. Bob is perfect for Duty 13)
Each morning, each employee needs to tackle 2 duties. the first is easy. I simply run an INDEX and MATCH on the table and run the
LARGE function for each employee. The trouble I'm having is when it comes to the second task. The 3 columns after task 2 are
me testing and failing. What I need is for it to assign the 2nd duty based on the criteria in the table, with no duplicates.
Basically the best man for the job with no repeated work. Can this be done or am I asking too much? Thanks in advance!

Steve

 

[marious]

=INDEX($L$3:$AE$3,1,LARGE((--($L4:$AE4=LARGE($L4:$AE4,E$2)))*(COLUMN($L$4:$AE$4)-11),1))

 

[StephenCrump]

Welcome to the Forum!

Here's one relatively simple way you could approach this:

	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
1
2		Employee	Task 1	Score 1		Employee	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t
3		A	i	10		A		7	8	4			3	8	10		8	8			9	7		6		4
4		B	m	10		B		8		7	5				5		9		10		9	9				7
5		C	j	10		C	5		3		6	2			7	10		10	9		10
6		D	e	7		D					7		7				4					7
7		E	b	6		E		6		5							4		3	6
8		F	f	9		F			2			9		9		3						9		4
9		G	l	10		G			5					5				10				10			6
10		H	h	9		H							8	9	4		6								7	6
11		I	n	8		I	2	4	4		2	6					2			8			4
12		J	a	7		J	7					9		5		8	5	7	7	3	3			7		5
13		Task 1	Average	8.6
14			Task 2	Score 2		Average	6.3
15		A	o	9
16		B	k	9
17		C	c	3
18		D	g	7
19		E	d	5
20		F	p	9
21		G	s	6
22		H	t	6
23		I	q	4
24		J	r	7
25		Task 2	Average	6.5
26
Sheet1

Cell Formulas
Range	Formula
C3:C12	C3	=LET(Ey,$G$2:$Z$2,GetBest(Ey,IF(ISNA(MATCH(Ey,C$2:C2,)),$G3:$Z3)))
D3:D12	D3	=XLOOKUP(C3,G$2:Z$2,G3:Z3)
D13,D25	D13	=AVERAGE(D3:D12)
G14	G14	=AVERAGE(G3:Z12)
C15:C24	C15	=LET(Ey,$G$2:$Z$2,GetBest(Ey,IF(ISNA(MATCH(Ey,C$2:C14,)),$G3:$Z3)))
D15:D24	D15	=XLOOKUP(C15,G$2:Z$2,G3:Z3)

GetBest: =LAMBDA(a,b,INDEX(a,MATCH(MAX(b),b,)))

This does the allocation sequentially by employee. You could flip this and process sequentially by task, which would produce different results. Neither would necessarily be the optimum solution - for that you'd need something more complicated to take into account the various possible combinations.

You may also need to vary the order in which you process the employees. In your example, Cropper can do only three jobs, so you'd better give him two of these before you start allocating jobs to other people.

 

[Zolkora]

=INDEX($L$3:$AE$3,1,LARGE((--($L4:$AE4=LARGE($L4:$AE4,E$2)))*(COLUMN($L$4:$AE$4)-11),1))
View attachment 71868

Thanks man, although I get the same issue here that I faced. The tasks only need done once. So in this example we have Ken and Gordon both completing Duties 7 and 10, albeit at separate times. I would like for it (In for example Ken's case) to see that Duty 7 is covered, so look for the next best task for that employee that isn't covered in the first column. So in his case it would be N4.

Cheers.

 

[Zolkora]

Firstly, I really appreciate you guys taking the time to help, really. I'm still facing the same obstacles. I can get it to check what tasks are performed for task 1 and avoid them, and then assign tasks for task 2. However at that stage there is some crossover. For task 2 in this instance i've got one duty assigned to 3 people on my sheet in its's current state. If I could run some formula after that, that could say something like "If there's duplicates then use whomever score was highest". I just don't know how. Agh, Sorry, and Thanks... again.

I'll try and share my document, if that helps.

Dynamic Skills Matrix.xlsx

Microsoft Excel Workbook

1drv.ms

 

[StephenCrump]

I stacked the two tasks in a single column, to simplify checking for duplicates. Your formulae for task 2 are now looking in the wrong place.

For example, here's your formula for D12 - it's checking for duplicates in the pink highlighted range C2:C23, not finding N4, and hence allocating N4 to Gary (even though it has actually been allocated twice already).

We could update my formulae to point to more then one column. But here's a simple patch instead, which just copies the Task 2 allocations to the end of column C.

Note that I have also tweaked the LAMBDA for GetBest, so it that only returns tasks with a non-blank score.

	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
1		Name	Task 1	Score	Task 2	Score
2
3		Ken	10	10	N4	7							DUTY
4		Gordon	7	10	11	6						Employee	2	4	5	7	8	9	10	11	12	13	14	15	N1	N2	N3	N4	PD1	PD3	PD4	DS
5		Bon	13	10	14	9						Ken	1			9	4	3	10	5	6					2	8	7
6		Bill	12	10	8	7						Gordon	2			10	3	4	9	6	5					1	7	8
7		Stewart	9	10	4	7						Bon	6	8	7		2	1		4	3	10	9									5
8		John	PD3	10	N2	8						Bill		2		4	7	8	3	9	10						6	5
9		Paul	PD4	10	N1	9						Stewart	4	7	8		9	10	5	3		2	1			6
10		Mat	5	10	???							John	3							2	1			6	7	8			5	10	9	4
11		Mark	PD1	10	15	9						Paul												7	9	5			6	4	10	8
12		Gary	N3	10	2	4						Mat		9	10								8			7
13		Christian	DS	10	???							Mark	8											9					10
14			Task 2									Gary	4	6	5	8			7								10	9
15			N4									Christian								8	9											10
16			11
17			14									Overall score	6.2
18			8									Av Task 1	10
19			4									Av Task 2	7.3
20			N2
21			N1
22			???
23			15
24			2
25
Sheet1

Cell Formulas
Range	Formula
C3:C13	C3	=LET(Ey,$M$4:$AF$4,GetBest(Ey,IF(ISNA(MATCH(Ey,C$2:C2,)),$M5:$AF5)))
D3:D13,F3:F13	D3	=IF(C3="???","",XLOOKUP(C3,$M$4:$AF$4,$M5:$AF5))
E3:E13	E3	=LET(Ey,$M$4:$AF$4,GetBest(Ey,IF(ISNA(MATCH(Ey,C$2:C14,)),$M5:$AF5)))
M17	M17	=AVERAGE(M5:AF15)
M18	M18	=AVERAGE(D3:D13)
M19	M19	=AVERAGE(F3:F13)
C15:C24	C15	=E3

GetBest: =LAMBDA(a,b,LET(m,MAX(b),IF(m=0,"???",INDEX(a,MATCH(m,b,)))))

 

[Zolkora]

Welcome to the Forum!

Here's one relatively simple way you could approach this:

GetBest: =LAMBDA(a,b,INDEX(a,MATCH(MAX(b),b,)))

This does the allocation sequentially by employee. You could flip this and process sequentially by task, which would produce different results. Neither would necessarily be the optimum solution - for that you'd need something more complicated to take into account the various possible combinations.

You may also need to vary the order in which you process the employees. In your example, Cropper can do only three jobs, so you'd better give him two of these before you start allocating jobs to other people.

This is great work, thankyou. I've pretty much replicated the sheet you made, but made some modifications relative to my circumstance. I have a question though.

Using your System, Gary would be carrying out tasks N3 and another lower scoring task. He scores highest on N3 then next highest in N4, so ideally i'd like him to do that. Is this because the spreadsheet is processing the results sequentially by employee and not by task? You mentioned that was possible. Is that an easy modification? I know I'm a pest lol.

 

[StephenCrump]

By employee:

	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
1
2		Emp	Task		Emp	Task1	Task2	Score1	Score2		Employee\Task	2	4	5	7	8	9	10	11	12	13	14	15	N1	N2	N3	N4	PD1	PD3	PD4	DS
3		Ken	10		Ken	10	8	10	4		Ken	1			9	4	3	10	5	6					2	8	7
4		Gordon	7		Gordon	7	N4	10	8		Gordon	2			10	3	4	9	6	5					1	7	8
5		Bon	13		Bon	13	14	10	9		Bon	6	8	7		2	1		4	3	10	9									5
6		Bill	12		Bill	12	???	10			Bill		2		4	7	8	3	9	10						6	5
7		Stewart	9		Stewart	9	2	10	4		Stewart	4	7	8		9	10	5	3		2	1			6
8		John	PD3		John	PD3	N2	10	8		John	3							2	1			6	7	8			5	10	9	4
9		Paul	PD4		Paul	PD4	N1	10	9		Paul												7	9	5			6	4	10	8
10		Mat	5		Mat	5	???	10			Mat		9	10								8			7
11		Mark	PD1		Mark	PD1	15	10	9		Mark	8											9					10
12		Gary	N3		Gary	N3	4	10	6		Gary	4	6	5	8			7								10	9
13		Christian	DS		Christian	DS	11	10	8		Christian								8	9											10
14		Christian	11
15		John	N2				Av	10.0	7.2
16		Gordon	N4				Min	10	4
17		Ken	8
18		Gary	4
19		Bill	???
20		Bon	14
21		Stewart	2
22		Mat	???
23		Mark	15
24		Paul	N1
Sheet1

Cell Formulas
Range	Formula
E3:E13	E3	=K3:K13
H3:I13	H3	=IFERROR(XLOOKUP(F3,$L$2:$AE$2,XLOOKUP($E3,$K$3:$K$13,$L$3:$AE$13)),"")
F3:G13	F3	=TRANSPOSE(FILTER(C$3:C$24,B$3:B$24=E3))
H15:I15	H15	=AVERAGE(H3:H13)
H16:I16	H16	=MIN(H3:H13)
C3:C24	C3	=LET(Task,$L$2:$AE$2,GetBest(Task,IF(ISNA(MATCH(Task,C$2:C2,)),XLOOKUP(B3,K$3:K$13,L$3:AE$13))))
Dynamic array formulas.

GetBest: =LAMBDA(a,b,LET(m,MAX(b),IF(m=0,"???",INDEX(a,MATCH(m,b,)))))

By task:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		Task	Emp		Emp	Task1	Task2	Score1	Score2		Task\Employee	Ken	Gordon	Bon	Bill	Stewart	John	Paul	Mat	Mark	Gary	Christian
3		PD3	John		Ken	10		10			2	1	2	6		4	3			8	4
4		PD4	Paul		Gordon	7		10			4			8	2	7			9		6
5		PD1	Mark		Bon	14	13	9	10		5			7		8			10		5
6		N2	John		Bill	9	11	8	9		7	9	10		4						8
7		N1	Paul		Stewart	2	8	4	9		8	4	3	2	7	9
8		15	Mark		John	PD3	N2	10	8		9	3	4	1	8	10
9		14	Bon		Paul	PD4	N1	10	9		10	10	9		3	5					7
10		13	Bon		Mat	4	5	9	10		11	5	6	4	9	3	2					8
11		2	Stewart		Mark	PD1	15	10	9		12	6	5	3	10		1					9
12		4	Mat		Gary	N3	N4	10	9		13			10		2
13		5	Mat		Christian	12	DS	9	10		14			9		1			8
14		7	Gordon								15						6	7		9
15		8	Stewart				Av	9.0	9.2		N1						7	9
16		9	Bill				Min	4	8		N2	2	1			6	8	5	7
17		10	Ken								N3	8	7		6						10
18		11	Bill								N4	7	8		5						9
19		12	Christian								PD1						5	6		10
20		N3	Gary								PD3						10	4
21		N4	Gary								PD4						9	10
22		DS	Christian								DS			5			4	8				10
23
Sheet2

Cell Formulas
Range	Formula
E3:E13	E3	=TRANSPOSE(L2:V2)
H3:I13	H3	=IFERROR(XLOOKUP(F3,$K$3:$K$22,XLOOKUP($E3,$L$2:$V$2,$L$3:$V$22)),"")
F3:F4,F5:G13	F3	=TRANSPOSE(FILTER(B$3:B$22,C$3:C$22=E3))
H15:I15	H15	=AVERAGE(H3:H13)
H16:I16	H16	=MIN(H3:H13)
C3:C22	C3	=LET(Ey,$L$2:$V$2,GetBest(Ey,IF(COUNTIF(C$2:C2,Ey)<2,XLOOKUP(B3,K$3:K$22,L$3:V$22))))
Dynamic array formulas.

I had a quick play with the order the tasks were assigned, and came up with these two possibilities, where 4 was the worst score. There may be more optimal solutions.