Checklist to find more than 0% and less than 100% hit rate

Hello ever helpful community. I need your help again, though this time not for myself.

We are trying to get a report of applications that are used by team members of each manager.
We have a list of what each team member uses and we need to see what apps are used by the team but not by every member.

So if Simon's team of 4 all use Adobe Acrobat then we don't need that to show up, but if only 3 of the 4 use it then we want to have a 'Y' to designate this in the relevent column on the UsageReport worksheet. And we don't care if none use it. So >0% and <100% we want a 'Y'. Everything else would best be left blank.

The worksheets:
Profiles worksheet contains our base data. Column G is the name of the staff member.
StaffReport worksheet is the source of linking the staff to the managers.
UsageReport is the end result listing the managers name and the applications used by only some of the members of their team.

StaffReport sheet can be used for a lookup to get an array of all members in the team of the manager (column F). The report for the Team Manager listed on UsageReport will include this manager and all staff listed to the right of him in the StaffReport sheet.

To make it easier, if Simon manages a team and is also in another team, he will be counted both times, this is fine, we don't need to filter this out.
To make it harder, some of the people listed as Team Manager on the UsageReport sheet are not listed in the source data areas so will error. These can just be left blank.

The attachment should make it easier to follow.
Hopefully I haven't explained myself badly again.

Hi

Any chance you could build a list of unique team managers, and their team members?

Same for the usage report - only have unique instances of the team mangers, not multiples.

rylo

DILIPandey, I edited my previous comment to add a comment too you. You're just to fast for me to keep up with hehe.
I admit to not verifying the quality of the data I was given. Those N's should not have been there at all. Easily fixed.

The original file has been sent back to the data analyst to clean up the duplicate records (some people have too many PCs). It is late here so I'll post it up tomorrow. Apologies.