How do I divide the work evenly between teammates

[Godiva]

Each number below represents a # of teammates forto give a KDE to manage (supervisor title). There are 4 KDE's to dispurse to. How do I divide the work evenly between them? This list may grow and shrink, as well as the numbers may be higher or lower (new hires, terminations), but the amount of KDE's will not change. It is my personal form, so it can be altered in any way. It is pulled from a report that shows team name along with the members of the team in rows. So I used COUNTA to count the # of rows of each team name to add how many are on it. This is painful and time consuming, as currently there are 21 teams, but coming soon there may be 2x as many.

So I pose this to you, Excel gurus, how do I

• Count the amt of people on a team by the name without manually entering the COUNTA in for each team?

• Divide out team count equally for 4 people?

Below is a hypothetical, small scope, example of what the list would look like. I used small numbers to represent the idea. They don't always add up evenly, but close as possible will work.

TEAM RED
TEAM RED
TEAM RED
TEAM RED 4
TEAM BLUE
TEAM BLUE
TEAM BLUE 3
TEAM GREEN
TEAM GREEN
TEAM GREEN
TEAM GREEN
TEAM GREEN 5
TEAM YELLOW
TEAM YELLOW 2
TEAM PURPLE
TEAM PURPLE
TEAM PURPLE
TEAM PURPLE
TEAM PURPLE
TEAM PURPLE 6


I'm using Excel 2010 in case you need to know. Thanks!

[icestationzbra]

for #1, you can use the function COUNTIF if you have just one criterion:

on to a separate column, extract all the unique values by using the Data > Remove Duplicates function; then, next to each unique value, place the following formula:

=countif(a:a,"TEAM RED")

if you have more than one criterion, use COUNTIFS.

the other, probably more utilitarian, method would be Pivot Tables. that function can be found on the Insert tab / ribbon (ALT > N > V > T).

for #2, i am unable to understand what you mean by "divide team count equally for four people"? may be, share a sample file with "before and after" data?

[Godiva]

I will try that idea later and update if it works, thank you

To better explain dividing them up equally...Each KDE will need to take as many teams as it takes until each one has the equal amount of teammates.

For instance:
THIS TEAM HAS THIS MANY PEOPLE - 13 - KDE #1
THIS TEAM HAS THIS MANY PEOPLE - 10 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 15 - KDE #1
THIS TEAM HAS THIS MANY PEOPLE - 14 - KDE #1
THIS TEAM HAS THIS MANY PEOPLE - 20 - KDE #1
THIS TEAM HAS THIS MANY PEOPLE - 16 - KDE #2
THIS TEAM HAS THIS MANY PEOPLE - 11 - KDE #2
THIS TEAM HAS THIS MANY PEOPLE - 13 - KDE #1
THIS TEAM HAS THIS MANY PEOPLE - 11 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 21 - KDE #2
THIS TEAM HAS THIS MANY PEOPLE - 20 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 6 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 18 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 13 - KDE #2
THIS TEAM HAS THIS MANY PEOPLE - 21 - KDE #3
THIS TEAM HAS THIS MANY PEOPLE - 10 - KDE #4
THIS TEAM HAS THIS MANY PEOPLE - 18 - KDE #4
THIS TEAM HAS THIS MANY PEOPLE - 19 - KDE #4
THIS TEAM HAS THIS MANY PEOPLE - 12 - KDE #4
THIS TEAM HAS THIS MANY PEOPLE - 14 - KDE #2
THIS TEAM HAS THIS MANY PEOPLE - 19 - KDE #4


KDE #1 75 people
KDE #2 74 people
KDE #3 85 people
KDE #4 77 people

I realize these aren't equally distributed numbers, but they're as close as my brain could get them to be -working on limited time-. Does this help explain better? I don't want to have to run through this list every time it grows & shrinks..... This list has to be distributed out weekly. thanks again!

[martindwilson]

maybe something like this

[Godiva]

Interesting Martin and thank you! I'm just wondering, does that break down teammates?

If a KDE takes one person from TEAM RED, then they have to take the whole team - that's the way our internal program is designed. I didn't make that clear, so sorry.