Aggregations of a matrix

[emeria]

I created a skills matrix with my team and was hoping to create a top 10 list of our highest scored skills vs lowest scored skills, and I am unsure of the best way to do so in Excel. I started by trying a filter (which I will include below).



A snippet of the matrix (smaller example)
image_2022-06-17_111437274.png

My FILTER I was using was to at least filter down to certain ranges. The G7 and I7 refer to thresholds that I set. The FILTER doesn't really do what I wanted, but it was the next best thing I could think of.

Please Login or Register  to view this content.

My ideal result would be something like this:

Top skills (X<5)
Accessibility Remediation
Apache
Accessibility Auditing
Accessibility (WCAG/508)

Bottom Skills (1<=X<=5)
.Net
Angular
ASP.NET MVC

No Skill (x<=0)
AWS
Ansible

Most of my google searches show me how to create a matrix or crosstab using pivots, but do not work with one that I already have.

Any ideas on methods to accomplish this?

[dosydos]

See yellow banner at the top with how to post a sample workbook. This makes all our lives easier so we don't have to recreate your data via a picture.

[emeria]

Thanks, added an example!

[Glenn Kennedy]

In the gfile, G7 & I7 are empty.

What do you expect to see? Where do you expect to see it?

Please mock up your expected results on the file.

[emeria]

In the gfile, G7 & I7 are empty.

What do you expect to see? Where do you expect to see it?

Please mock up your expected results on the file.

Sorry about the confusion, the G7 and I7 referred to are on the sheet "Skill Views". The formula using G7 and I7 is in B8. (The other two filters are in B16 and B24 of the Skill Views sheet)

My desired results are on the sheet "ExampleDesiredResults". I want to be able to see my top X or bottom X skills, as well as be able to find skills that we have no proficiency in (shown in B2 to B15). Ideally, I could see a unique list of what team members have skills for that list of skills (shown in B21 to B25). I included a screenshot on that sheet, that just breaks the problem down to a smaller set of skills.

Let me know if you still want me to improve the example workbook or if you have more questions.