Split a team into sub teams and limit the number of participants

[AmitI94]

Hello All!

I’ve a dataset of 80k records, with a column “Team Name” containing about 400 distinct team values. But now, I want to create a new column called “Sub-Team” and split each of the big teams into smaller teams containing 20 or fewer people. Any ideas as to how I can do it?

e.g.

Team Name Emp-Name

        A              XYZ

        A              QRS

output:

Team Name           Sub-Team Name      Emp-Name

       A                               A1                        XYZ

Thus Team “A” with 400 people gets split into 20 sub teams A1,A2,A3..A20 and in case the number is not a multiple of 20, some teams may have more or less than 20 people 

[ShankerV · Accepted solution]

Hi @AmitI94 

You can easily achieve with the help of Tile tool for splitting into groups.

Then use the formula tool to create a new row Sub-Team Name where you append Team Name + Count 1 which will help to get A1, A2, A3 and so on then B1, B2 etc.

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

[AmitI94]

Can you please elaborate on the usage of the tile tool, there seems to an input for numeric column, but not able to understand its purpose. It also needs tile cutoffs but would I have to add 20,40,60…. For all the cutoff points?

[KrishnaChithrathil · Accepted solution]

@AmitI94 

Are looking for something like this? If not pls provide us an input and expected output separately.

[AmitI94]

Hello Krishna, yes but here how do I incorporate the logic that there should not be more than 20 people in one team?

[KrishnaChithrathil]

@AmitI94 

If you've let's say, 500 people in team A, on what basis you're grouping it to 'not more than 20 people' in team A. My apologies. I still didn't get that logic in between. Am I missing out something?

If possible give more random input data and expected output. 

[Christina_H]

How about this - I've generated "teams" with different numbers of people, calculated how many subteams are required, and assigned people to them.

[AmitI94]

So I don’t have any specific logic, I just want to limit the number in each team to 20 or less

[KrishnaChithrathil]

@AmitI94 

how about this then?

[AmitI94]

Hello all! I resolved it by using the file tool, and used the second column and divided it by 20 and used its ceiling for the team number hence all teams have upto 20 members! Thanks a lot!