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

jcsantos

New Member
Joined
Nov 5, 2018
Messages
5
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 1Post 2
Skill 1X
Skill 2XX

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

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

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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
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

New Member
Joined
Nov 5, 2018
Messages
5
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 1Post 2
Skill 1X
Skill 2XX

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

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

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

It was wrong
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
How about:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNO
1Post1Post2Post3Post4Post5Post6Post1Post2Post3Post4Post5Post6
2Skill1XXXXWorker1X
3Skill2XXXXWorker2X
4Skill3XXXWorker3XX
5Skill4XXWorker4XX
6Skill5XXXXWorker5XXXXXX
7
8
9
10Skill1Skill2Skill3Skill4Skill5
11Worker1XX   
12Worker2 X   
13Worker3XX   
14Worker4XXX X
15Worker5XXXXX
16
Sheet29
Cell Formulas
RangeFormula
B11:F15B11=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
Or a somewhat shorter formula:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNO
1Post1Post2Post3Post4Post5Post6Post1Post2Post3Post4Post5Post6
2Skill1XXXXWorker1X
3Skill2XXXXWorker2X
4Skill3XXXWorker3XX
5Skill4XXWorker4XX
6Skill5XXXXWorker5XXXXXX
7
8
9
10Skill1Skill2Skill3Skill4Skill5
11Worker1XX   
12Worker2 X   
13Worker3XX   
14Worker4XXX X
15Worker5XXXXX
Sheet29
Cell Formulas
RangeFormula
B11:F15B11=IF(MMULT(--(J2:O6<>""),--TRANSPOSE((B2:G6<>""))),"X","")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,365
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top