Combine two matrixs into one global one that crosses the info from both

[jcsantos]

Hi,

I am trying to cross the info of people, work post and training.

In sheet 1 I have a table which identifies with an X which training the work post needs.

	Post 1	Post 2
Skill 1	X
Skill 2	X	X

In sheet 2 I have a table which identifies with an X which post each person is able to perform.

	Post1	Post 2
Worker 1	X
Worker 2		X
Worker 3	X	X

Now based on those two tables, I want to automatically create a matrix that combines the info and retrieves me

	skill 1	skill 2
Worker 1	X
Worker 2		X
Worker 3	X	X

 

[Eric W]

Shouldn't the result for Worker 1 be X in skill1 and skill 2, since Worker 1 can do Post1, and Post1 requires skill1 and skill2?

 

[jcsantos]

Hi,

I am trying to cross the info of people, work post and training.

In sheet 1 I have a table which identifies with an X which training the work post needs.

	Post 1	Post 2
Skill 1	X
Skill 2	X	X

In sheet 2 I have a table which identifies with an X which post each person is able to perform.

	Post1	Post 2
Worker 1	X
Worker 2		X
Worker 3	X	X

Now based on those two tables, I want to automatically create a matrix that combines the info and retrieves me

	skill 1	skill 2
Worker 1	X	X
Worker 2		X
Worker 3	X	X

It was wrong

 

[jcsantos]

Shouldn't the result for Worker 1 be X in skill1 and skill 2, since Worker 1 can do Post1, and Post1 requires skill1 and skill2?

Thanks

 

[Eric W]

How about:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Post1	Post2	Post3	Post4	Post5	Post6			Post1	Post2	Post3	Post4	Post5	Post6
2	Skill1	X			X	X	X		Worker1	X
3	Skill2	X	X			X	X		Worker2		X
4	Skill3			X	X		X		Worker3	X	X
5	Skill4			X			X		Worker4		X		X
6	Skill5			X	X	X	X		Worker5	X	X	X	X	X	X
7
8
9
10		Skill1	Skill2	Skill3	Skill4	Skill5
11	Worker1	X	X
12	Worker2		X
13	Worker3	X	X
14	Worker4	X	X	X		X
15	Worker5	X	X	X	X	X
16
Sheet29

Cell Formulas
Range	Formula
B11:F15	B11	=IF(SUMPRODUCT((INDEX($J$2:$O$6,MATCH($A11,$I$2:$I$6,0),0)="X")*(INDEX($B$2:$G$6,MATCH(B$10,$A$2:$A$6,0),0)="X")),"X","")

 

[Eric W]

Or a somewhat shorter formula:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Post1	Post2	Post3	Post4	Post5	Post6			Post1	Post2	Post3	Post4	Post5	Post6
2	Skill1	X			X	X	X		Worker1	X
3	Skill2	X	X			X	X		Worker2		X
4	Skill3			X	X		X		Worker3	X	X
5	Skill4			X			X		Worker4		X		X
6	Skill5			X	X	X	X		Worker5	X	X	X	X	X	X
7
8
9
10		Skill1	Skill2	Skill3	Skill4	Skill5
11	Worker1	X	X
12	Worker2		X
13	Worker3	X	X
14	Worker4	X	X	X		X
15	Worker5	X	X	X	X	X
Sheet29

Cell Formulas
Range	Formula
B11:F15	B11	=IF(MMULT(--(J2:O6<>""),--TRANSPOSE((B2:G6<>""))),"X","")
Press CTRL+SHIFT+ENTER to enter array formulas.