Survey Data analysis to get top 5 skills to improve based on multiple criteria

[jb00976]

Hi

I have data that come from Forms in Teams where individuals will be evaluating their employees skills. The data will look similar to the chart below but, with more questions (likely 20). Each question only has 3 possible responses: Demonstrates Mastery, Demonstrates Proficiency, and Requires Support. I need to be able to show in excel the top 5 questions that were answered "Requires Support" (ie. the skills to improve). I also need to be able to allow users to filter by all of the other none question fields (the green columns) and get the top 5 questions answered "Required Support" for those.

For example: I want to allow users to take the data and filter by:

1)Title = Title A
2)Region = North Region
3)Tenure with company = 0-12 month

and the excel returns what are the top 5 questions that they answered "Requires Support" on.

How would I put this together? I was playing around with Pivot tables and wasn't getting the results I wanted. Thank you in advance for any help

 

[alansidman]

We cannot manipulate data in a picture. Please provide a sample using XL2BB (8-10 records) or post your file to a third party like dropbox.com or box.net. Also, please update your profile to include the version of XL that you are using. Members here will provide answers based upon the version of excel you are using. Help us to help you.

 

[jb00976]

Sorry here is a link to the example data in excel. I am using the 365 desktop version of excel.

Example data

 

[alansidman]

Will this always be your filters
1)Title = Title A
2)Region = North Region
3)Tenure with company = 0-12 month
and will you always want to filter for "Requires Support?"

Or will you want to filter on other factors. This is important as to how a solution is drawn.

 

[jb00976]

Will this always be your filters
1)Title = Title A
2)Region = North Region
3)Tenure with company = 0-12 month
and will you always want to filter for "Requires Support?"

Or will you want to filter on other factors. This is important as to how a solution is drawn.

Filters are the items in green: Manager, Name, Province, Region, Title, Tenure in current role and tenure with the company. For the questions we are looking to find out the ones where individuals indicated "Requires Support" so that we know which areas we need to focus training on.