You're an Expert? Check this out! (Weighted Distribution in Groups)

[Hawajiko]

Hello Everyone,

Sorry for the clickbait.

I have combed the internet to find a solution for this but I was unable to come up with something that addressing my requirements. They are as follows.

1. There is a list of users which can be even or odd in number (preferable in a separate sheet since the list grows and decreases on a daily basis based on attendance so changes would automatically be catered in distribution if rows are deleted)
2. Each user has a certain weightage value associated to it (1 to 10 or decimal value like 2.1 and 2.4).
3. At the time of running the users in the list will be distributed in two groups.
4. Each group should have as close to equal weightage distribution as possible.
5. All the user in the list should be accounted for in the group irrespective if one group has an additional user, the total weightage should be close to equal.

I have been able to find some websites that can do that like the following however, their weightage distribution is from 1 to 5 which is not enough to assess skill value since someone who is a 2.1 score would be equal to a 2.4 score rounded off.

Keamk - Create random and balanced teams

Looking for assistance regarding this. I also tried using the following excel file create and shared by "Nothing Left to Lose" name "Assign_Teams" from Microsoft forum (Redirecting) but it has some issues like if an equal/exact is not possible, it will not work, it will also create groups of equal number of members not one additional in one or the other team if the total count is an odd number.

Any help in this would be greatly appreciated.

Thanks,
Hawajiko

 

[KRice]

Three questions:
1. Based on item #5 in your list where you mention "if one group has an additional user", it is a requirement for the two groups to have the same number of users (except for when there is an odd number of users in the starting list)? So n1-n2=0 or 1 always?
2. What is the approximate total number of users in the starting list.
3. By "total weightage", do you mean the sum of the weighting factors for all users in a group?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: You're an Expert? Check this out! (Weighted Distribution in Groups)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Hawajiko]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: You're an Expert? Check this out! (Weighted Distribution in Groups)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My apologies, I read the first part of the rules and thought it was ok for that.

I posted this on the following.

1. You're an Expert? Check this out! (Weighted Distribution in Groups)

I couldn't find the edit post option, please assist so I can do that on the main message as well.

Thanks,
Hawajiko

 

[Fluff]

No need to add it to to the initial post on this occasion.

 

[Hawajiko]

Three questions:
1. Based on item #5 in your list where you mention "if one group has an additional user", it is a requirement for the two groups to have the same number of users (except for when there is an odd number of users in the starting list)? So n1-n2=0 or 1 always?
2. What is the approximate total number of users in the starting list.
3. By "total weightage", do you mean the sum of the weighting factors for all users in a group?

Please see the sample that I believe would answer your questions.

Of course the last type would be equal group/unequal group with exactly equal score.

The goal is create a set number of groups based on input like 1 or 2 or 3 and generate groups based on that.

Any assistance will be greatly appreciated.

Thanks,
Hawajiko

 

[KRice]

Thanks for this information. So if I'm following correctly, the answers to my questions are:
1. yes, we want the number in each group (n1 and n2) to be abs(n1-n2)<=1...meaning to be split as evenly as possible
2. the total number of users in the starting list is typically about 30, give or take...is that true?
3. "total weightage" appears to be the sum of the "scores"

Is it always true that only two groups need to be created?...you never have need for forming 3 or more groups?

 

[Hawajiko]

I appreciate your input and time Krice. Please find the response to your questions below.

1. Yes, indeed you are correct in your understanding. There is one caveat thought, there could be a maximum of 1 additional member in a group compared to others. like 11/10, 11/10/10, 9/9/8 etc.
2. The master list of users is a sample and it could increase/decrease over time. The groups are based on users in attendance. So far the maximum I have seen are 22 in attendance and the maximum count to be 33.
3. That is correct.

 

[Hawajiko]

Perhaps just consider the master list for the source of data.
I will add/remove people manually to it and the solution would accommodate it when generating groups.

 

[Fluff]

Now posted here as well Group Distribution based on Weightage/Score despite the fact that somebody here is trying to help you.

@Hawajiko In future please post links to ALL sites where you have asked the same question.