Align 100 entries, to 25 people. Can it be done in excel?

[noslenwerd]

Hard to explain but let me try

We took a survey on our team, asking what subject matter they are comfortable with. There are 100 topics, and there will be redundancy as far as employees selecting the same topic. re: John Doe, Jane Doe, and Jim Doe selected Topic 1, 2, and 3.

For round numbers, let's say we have 25 people on the team, and 100 "topics." We need to "assign" 4 topics to each person (to cover the 100). Some topics only have one person who said they are comfortable with it. That is easy to "assign" to a person. The main objective is to be sure we have
- Only 4 topics per person
- Having every single topic aligned to an employee.

Can this be done with a pivot table? Formula? Appreciate the help!

Here is an example of the kind of data I am working with.

 

[Psygrrl88]

A few questions; How many people (max) are you wanting to assign to each topic? Is there a max. I.e. if everyone is comfortable with SEO can everyone HAVE SEO?
can you assign topics someone isn't comfortable with to that person?
What is the (Min) number of people you need to assign a particular topic to?
-From your info My guess is each topic only gets assigned to one person, and you are just trying to do the "BestFit" scenario.
also - is there a seniority issue to be worried about (i.e. Jon is super comfortable and experienced with SEO, so he should get SEO, while Jane is comfortable with it but not as good as John)

 

[noslenwerd]

A few questions; How many people (max) are you wanting to assign to each topic? Is there a max. I.e. if everyone is comfortable with SEO can everyone HAVE SEO?
can you assign topics someone isn't comfortable with to that person?
What is the (Min) number of people you need to assign a particular topic to?
-From your info My guess is each topic only gets assigned to one person, and you are just trying to do the "BestFit" scenario.
also - is there a seniority issue to be worried about (i.e. Jon is super comfortable and experienced with SEO, so he should get SEO, while Jane is comfortable with it but not as good as John)

Thanks for the reply.

First question. We only want each topic assigned once. ZERO overlap. 100 topics, 25 people, end result of 4 topics for each person. On the survey all 100 topics had at least one person fill out "yes" to the topic.

Min and Max would be 4.

Correct. We want best fit scenario.

Seniority: No. The questions were phrased as "If you feel you are an expert in this topic, please check the box"

Main focus is really on getting an even split for the employees, and assurance all topics are covered.

This may help. Here are some of my thoughts I shared with my team. Our team members are called "Specialists" by the way.

Once we get columns for each topic, that show all of the specialists who selected the topic. Next step would be to sort those columns in ascending order (lowest total people selecting a topic first). Then giving each specialist a "score"... that score will be based on how many topics they selected total.

For instance. Let's assume SEO has 2 people total. Those two people are Nicole and Marlon. Nicole has a score of 3.5 (she selected yes to 35% of the topics), and Marlon is a 2.7 (selected 27% of the topics).

Logic would dictate that we want the person with the least amount of total topics selected, to get assigned that topic. That would essentially be the tie breaker. Then we work our way up to the topics that have more larger amounts of people who selected that topic. The logic should start to get easier from there.

We would also include logic like "IF specialist total topic count > 3, THEN assign to next available specialist"

 

[Psygrrl88]

so - while I am fairly good at sorting through data, unless you will be doing this fairly often - I think the coding and tables required to "automate" the process would be more complicated than just having someone do it manually.

Manually I would use a pivot table/tables to organize the collected data and as you said assign each person a score for comparison. Then go topic by topic and assign each one starting with the topics with the least amount of people.

I could be wrong though, maybe someone else will chime in.