Skills Matrix Comparison in Excel

[WAMMatrix]

Good afternoon all,

I've created a Skills Matrix in Excel where engineers listed in columns can select their skill set against each application. They can either choose NONE, BASIC, INTERMEDIATE and ADVANCED (An Example is shown in the Skills Matrix Diagram.jpg attachment.

Now, what I want to do, is under another Tab (titled Statistics), my manager can select an engineer from the drop down list, and it will display the same column that it does in the skills matrix but for ever application that the engineer has selected NONE or BASIC, it will then display engineers in another column (running parallel) that have INTERMEDIATE or ADVANCED skills for that app.

The idea being that my manager can see who can provide training to those with No and Basic skills (as shown in the Comparison Example.jpg.

 

[Rijnsent]

Hi WAM,
the simplest start is a MATCH formula to find out for each row if/when the first occurance of "INTERMEDIATE" is. For 7-zip that's 3 (the 3rd column). With an OFFSET formula you could than pick up the name of that person (using the MATCH result).
If you want a list of employees, that's a bit harder. You'd need an array formula with a TEXTJOIN / concat function (tried searching for it, but couldn't find a quick tutorial).
Cheers,
Koen

 

[WAMMatrix]

Thank you so much for your response. I'll give it a try.

Really appreciate your input.

 

[sandy666]

something like this?

or

 

[WAMMatrix]

Yes... absolutely.

 

[sandy666]

So use Power Query (Get&Transform)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Application Name"}, "Attribute", "Value")
in
    Unpivot

then Insert Pivot Table
- use an external data source
- choose Query - Table1
ok
and add fields to appropriate areas