• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted Distribution in Groups

Status
Not open for further replies.
H

Hawajiko

New Member
Hello Everyone,

Apologies 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. When using it, the users in the list will be distributed in 2/3 groups (group count could be changed from a cell).
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 1.6 score would be equal to a 2.4 score rounded off.
Link > https:/ /www.keamk.com

Looking for assistance regarding this. I also tried using the following excel file create and shared by "Nothing Left to Lose" from Microsoft forum (https://answers.microsoft.com/en-us...on-excel/4477e3e6-1467-4d62-a8b2-7d5cfc5c57b6) 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
 

Attachments

  • Assign_Teams.xlsm
    40.6 KB · Views: 6
Hawajiko
You've opened Your thread Ask an Excel Question-forum.
Are You sure that above could do based formulas?

Your given ... file's VBA is protected.
What for is Your given link to somewhere?

Could You send Your own Excel-file,
which shows
what do You really would like to get?

Please Reread Forum Rules.
 
I may have not articulated properly.
I am a beginner and only do stuff based on google search which normally is how to handle things with formulae.
Anything I have shared in the main post is based on google search and what I could find.

Please find the sample below of what I am looking for. The number of groups could be either 2 or based on input.

80124
 
Hawajiko
Did You reread Forum Rules?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

    Instead of picture, attach Your own Excel-file as You've noted.
    Pictures are challenge to modify.
Moderator Note:
Hawajiko
Seems that You have few time skipped Forum Rules.
Cross-Posting was one point - why You've asked to read those.
 
Last edited:
As AliGW had already posted in the section about the cross-posting, I didn't mention it.
Please find the excel file corresponding to the image.
 

Attachments

  • Book2.xlsx
    10.5 KB · Views: 2
You should have mentioned it at the start - in your opening post. I was merely reporting it.
 
In the attached, in Sheet1 (2), a button which tries to do your distribution.
The table on the left is a proper Excel Table whose extent you can see at the bottom right:
80138
If necessary you can grab this handle and adjust the size of the table.
The macro uses the table extents to decide what raw data to work on.
The macro also looks at cell E2 (green) to decide how many groups to distribute into.
The macro first sorts the table by Score descending, then works down the table choosing to add each row of the source data to the group which, at the time, has the lowest total score. It finishes off by adding a total at the bottom of each group.
It's a start and doesn't seem to do too badly with your sample data, even if you choose 20 groups.
 

Attachments

  • Chandoo48449.xlsm
    28 KB · Views: 6
Last edited:
That's amazing P45cal. This pretty much sums the basic gist of it.

The grouping is pretty much balanced however at times the score difference between each is more than 5 where this creates a little advantage for one group.

Additionally, can you please add a randomizing option as well? Each press of the button while keeping the same number of users will randomizes the grouping again? At the moment, it's one possible combination being displayed.

Thanks for your time and effort.
 
Please find the screenshot below of a dataset I used.

80147

Additionally, what I had in mind was there are multiple people having same score (please make them whole number for ex. 1 to 10 instead of decimal values, I didn't realize I moved the decimal points back to show whole numbers) so let's say in the above example (assuming it was close to/equal) User 23 in group 1 would be replaced with User 24 in Group 3, User13 in Group 2 would be replaced with User 14 in Group 1. The end result would be the same in terms of total score but the users would be shuffled here and there. I am sure there would be multiple combinations coming in where more than one of them would have different group members but same score.

One more question would be, can you make the sheet input data like below? All the users who are marked 'x' would be counted and remaining skipped in group creation?

80146

Once again, thanks for your time and effort.

Hawajiko.
 
I'm not at a computer right now, but could you save me some time tomorrow by attaching a file rather than a picture of one? And while you're at it, why not make them whole numbers yourself?
 
Thanks for your response P45cal.
I attached a screenshot because I was worried, I may mess up the functionality at the backend.

Please find the updated file for your consideration.

I made the following changes.

1. Changed the score of users into whole numbers.
2. Set the user selection to show the score deviation of 4 to 5 points between groups.
3. Added another sheet where I manually moved users between groups to achieve a perfect balance of score between all groups.

I think (I could be wrong) the solution generates the first group and then attempts to balance the next group based on the previous one which is causing the disparity.

Edit: Perhaps when a randomizer is added to the solution, it may alleviate this issue?

Thanks,
Hawajiko
 

Attachments

  • Copy of Chandoo48449.xlsm
    29.6 KB · Views: 2
Last edited:
In the attached I've added a Present column for an x if the person is present.
I've also added a random column for sorting on.
Look at the comments in the SortMe macro to trial various options, such as sorting purely randomly (which generally gives poorer results).
As I've left it, it will randomly move the users with the same weighting about. This means it will not change the scores, but it will change the Users with the same scores around.
Hawajiko said:
I think (I could be wrong) the solution generates the first group and then attempts to balance the next group based on the previous one which is causing the disparity.
Click to expand...
No, as mentioned in msg#9, after sorting, it works from the top and adds the User and his value to whichever group currently has the smallest total. I'm pleased to discover that someone else has thought along the same lines and that this algorithm is called Greedy Number Partitioning, see https://en.wikipedia.org/wiki/Greedy_number_partitioning where it says: "Greedy algorithms process the numbers sequentially, and insert the next number into a bin in which the sum of numbers is currently smallest." This means that no attention is paid into getting more or less equal numbers of participants in each group. Look at the sheet Oops in the attached; equality of scores is achieved but equality of members isn't.

While your sample on sheets Sheet1 (2) and Sheet2 shows that both equality of numbers and equality of scores can be achieved with that particular sample, the Greedy algorithm is by no means guaranteed to give you the best outcome.
When I look at the other programming possibilities I notice 2 things:
  • It's difficult to programme for
  • It can take a very long time for such a routine to determine the better outcomes despite a speedy machine.
As a result I don't propose to write such a routine! Sorry.
It will probably (depending on the numbers of groups and numbers of users) be faster to use the Greedy algorithm and then examine the results and do some juggling. To help you do this, in cell J1 of the Sheet1 (2) sheet there's a formula to give you the ideal (target) score of each group.
 

Attachments

  • Copy of Chandoo48449.xlsm
    38.2 KB · Views: 8
Thanks for all the support and assistance P4scal. This pretty much sums it up for me.

You have been amazing and very prompt. Salute! to your expertise.

Just wanted to understand the logic behind this. Correct me if I am wrong please.

1. Total present user list is sorted in descending order 10 to 1
2. One by one a user is added to each group (assuming there are 2 groups to be created)
a. If there are 3 users with score of 10 (User 1, User 2, User 3)
b. User 1 goes to Group 1, User 2 goes to Group 2, User 3 goes to Group 1
c. All the users are distributed this way
d. Once all the users have been assigned, users with same scores will be can be shuffled.

Edit: How do I mark this as resolved?
 
Last edited:
Step by step (2 is in cell E1 so we're only distributing into 2 groups):
Situation after sorting (the source data is sorted first by Score, ascending, then when there are equal Scores, those are sorted according to the random number in the rnd column):
80161
(You'll notice that the top 3 10 score values are sorted by the rnd column descending. The rand() function is a volatile function so you may see its value change while the macro runs; it doesn't matter, because the first 3 columns are now in the order they're going to be processed, and they'll stay that way.)
Looking at the 2 groups on the right, they're both empty. Both their running score totals are zero. Our job is to put the first value in the group with the smallest total score - in this case, they're both the same so the first one (Group 1 on the left) is the one which will receive the first row from the source data.
Here it is, done:
80162
Straight away, we can see that the running total for group 1 is 10, and that for group 2 is 0, so we now know that the next value from the source list goes into group 2 because it has the smallest running total. Here it is:
80163
Again, straight away, we can see the running total for both groups is 10, they're equal, so group 1 is going to get the next value. We don't care what that value is; it's another 10:
80164
Group 1's running total is now 20, Group 2's is 10, so the smaller is 10, Group 2, which will receive the next value:
80165
Now Group 1's running total is 20, Group 2's is 19, so the smaller is 19, still Group 2, which will receive the next value:
see picture in next message (I can only have 5 pictures per message)
 
Last edited:
…continued:
80166
Now Group 1's running total is still 20, Group 2's is 28, so the smaller is 20, Group 1, which will receive the next value:
80167
and so on.
 
That's actually a very simple logic.

Hats off to you my friend. You've been very patient in helping me out and the laymen questions I was putting up.

You're an MVP in my book mate.
 
Status
Not open for further replies.
Back
Top