=ROUNDUP(RANK($C27,Rando)/Sizes,0) FORMULA

[samyscraps01]

I tried the formula below
=ROUNDUP(RANK($C27,Rando)/Sizes,0) and it works great for a list of people. I need this formula to work for a group of employees rather than a list of names. My chart below shows a group of employees in each department. So 42 represents one department, 19 another group of employees in another dept, and so forth. I want the formula to group together as evenly as possible all the departments and distribute them evenly into 6 main groups to be disbursed amongst 6 leaders. This is more than just taking the total of all the employees and dividing them into six even groups. I need as many departments grouped as evenly as possible into six buckets. Is there a way to do this? Even with another formula. I tried this formula with a list of employees and it worked really well but when I applied it to the EE's in Column B I did not get the same results.

Dept No.	EE	Random	Buckets	Name List
91230	42	0.583587226	3	Sizes	6
91231	19	0.829519086	1
91232	35	0.393358531	3
91233	57	0.775741312	2
91234	25	0.141453984	4
91235	23	0.023805453	4
91236	39	0.511275187	3
91237	47	0.212964913	4
91238	7	0.23715932	4
91239	3	0.774636135	2
91240	18	0.52070436	3
91241	17	0.300847994	4
91242	2	0.841505214	1
91243	36	0.789073292	1
91244	101	0.825132871	1
91245	13	0.886795114	1
91246	15	0.704440553	2
91247	28	0.733682216	2
91248	4	0.280399365	4
91249	48	0.326471943	3
91250	1	0.576338148	3
91251	12	0.695422989	2
91252	21	0.676249387	2
91253	14	0.857909903	1

<tbody>
</tbody>

 

[Gerald Higgins]

...group together as evenly as possible all the departments and distribute them evenly into 6 main groups...

What exactly does this mean ?

What criteria do you want to use to group departments together ?

Can you give us a worked example of what the results should be, with that sample data ?
And explain how you arrived at those results ?

 

[samyscraps01]

Thank you Gerald for responding. I have been up since midnight trying to troubleshoot this problem.

MGR DEPT. NAME DEPT. NO. # OF EMP

SAM	PAYROLL	90010	26
SAM	HR	90028	8
RALPH	FACILITIES	90016	36
LYNN	CARDIO	90037	2
LYNN	LAB	90039	8
LYNN	EDUCATION	90020	3

<tbody>
</tbody>

Sorry, let me see if I can explain a little better. I was tasked with taking a list of Managers (Column A), and trying to group them together into 6 different groups. The catch is that each manager has multiple departments (column B /C) under their scope and furthermore the number of employees varies in each department. I need to distribute this list to 6 different leaders, and each leader wants to get a list of close to the same amount as the next leader. They would like to keep as many as the same managers in each bucket so they aren’t splitting managers.

Does that make sense? This is complicated because the number of employees varies. I got a decent formula but it did not give me an even split and same managers were distributed in multiple buckets. I can’t have that. Essentially each leader prefers to work with the same set of managers and still retain an even amount of employees.

Is this possible?

 

[Gerald Higgins]

Well the data in post #3 looks different to the data in post #1 , because the department numbers are all different.

What would really help is to show in a single post, a sample of the source data, AND what the results should be FOR THAT source data.

I think I get roughly what you are trying to do, but I guess there are some specific criteria here that you want to work with and I'm not getting those.

 

[samyscraps01]

Post 1 was me trying to explain this and clearly I failed miserably. Let’s stick with Post 3, I think that’s a little more straightforward.

MGR	DEPT. NAME	DEPT. NO	# OF EMP
SAM	PAYROLL	90010	26
SAM	HR	90028	8
RALPH	FACILITIES	90016	36
LYNN	CARDIO	90037	2
LYNN	LAB	90039	8
LYNN	EDUCATION	90020	3

<tbody>
</tbody>

Ideally, I would like the results to look something like this:

Bucket 1 :
Sam - 26
Sam – 8
Total: 34

Bucket 2:
Ralph – 36
Total: 36

Bucket 3:
Lynn -2
Lynn -8
Lynn -3
Total: 13

(Bucket 3 -this is just a sample, Ideally I would like this bucket to contain about the same as the others). For the sake of it, let’s say the total here was 32 instead of 13 as outlined above in the chart. This means that all three buckets, contain about the same number of employees without separating the managers into different buckets. The goal is for the 6 leaders to work with the same group of managers. So, leader 1 would get Bucket 1. What they don’t want is to be working with Sam or any other manager appearing in different buckets, because that means they will be under different leaders. The idea is to keep them in the same buckets while still maintaining an equal or close to equal employee count in each bucket.

If this helps my total employees add up to 1240, and I have 18 different managers. I need 6 different buckets with roughly or as close as possible to same distribution of employees in each bucket. I’ve tried different formulas and I never get an even amount in the buckets or even approximate. I can work with close ranges, eg. 160, 165, 163 or something to that effect but I get stuff like 50, 175, 10, random stuff. That won’t work.

Does this make more sense?

Thank you for your help and your patience.

 

[Gerald Higgins]

If your source data already looks like the top table in post #5 , then I don't see what the challenge is . . . it's already been divided up into manager bundles.

Am I missing something ?

 

[samyscraps01]

What your seeing is manual and I have a lot of data. There are over 1200 employees, I only included a sample of what my source data looks like and you said to give you an idea of what kind of results i'm looking for. There is too much data to do this manually, can it be done with a formula? I guess i'm getting very skeptical that this can be done. If I do this manually it will take me forever moving employees around to get six even buckets while ensuring that the managers are the same in each bucket.

Does this make sense?

 

[Sam_D_Ben]

Confused, Please upload a screenshot of your workings to get an idea.

 

[Gerald Higgins]

OK so you've got 1,200 employees.
Imagine instead that you had only 20 employees, and let's say 3 or 4 managers.
What would the data look like for those 20 employees ?
How would you allocate them to buckets ?
Post the data for all 20 employees as it would look at the start, and then post the results you want to see.

 

[Eric W]

If I understand correctly, you want to assign 18 managers to 6 buckets such that the number of employees per bucket is roughly equal. This really can't be done with formulas. What you're asking is to calculate hundreds, thousands, up to 6^18 (101,559,956,668,416) combinations and find the "best" combination. A pretty tall order. Possibly your best option is to use Solver. Set up your worksheet like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	MGR	DEPT. NAME	DEPT. NO	# OF EMP		Manager	# of Emps	Group		# of groups	Group	1	2	3	4	5	6
2	SAM	PAYROLL	90010	26		Sam	34	1		6	Count	46	76	35	106	30	19
3	SAM	HR	90028	8		Ralph	36	2
4	RALPH	FACILITIES	90016	36		Lynn	13	3			Max count - min count
5	LYNN	CARDIO	90037	2		Al	81	4			87
6	LYNN	LAB	90039	8		Belle	30	5
7	LYNN	EDUCATION	90020	3		Clarisse	19	6
8	Belle			27		Dan	12	1
9	Fred			3		Ed	40	2
10	Ed			22		Fred	22	3
11	Dan			2		Gayle	25	4
12	Clarisse			19
13	Al			25
14	Dan			10
15	Fred			19
16	Gayle			25
17	Al			29
18	Al			27
19	Belle			3
20	Ed			18

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula G2 =SUMIF(A:A,F2,D:D) L2 =SUMIF($H:$H,L1,$G:$H) K5 =MAX(L2:Q2)-MIN(L2:Q2) <tbody> </tbody>

<tbody>
</tbody>

I added several more managers to test with, since with only 3 managers, there are very few options. Put the complete list of managers in column F. Put the G2 formula in and drag down. Then column H will be the group that the manager will be assigned to. For now, just randomly put values in the list. Fill out J1:K2 as shown. Put 1-6 in L1:Q1. Put the L2 formula in and drag to the right. Finally, put the K4 heading in, and the K5 formula.

Group 4 has 106 employees, and 6 has 19, for a difference of 87 (K5). Not very close. So let's try Solver. Solver is included with Excel, but it's not installed by default. Make sure you have it installed by clicking on File > Options > Add-ins > On the bottom where it says "Excel Add-ins" click Go... > Check the Solver Add-in box > OK. Now go to the Data tab and click Solver on the far right.

It'll open up a dialog box. Enter the parameters as follows:

Set Objective: $K$5
To: Min
By Changing Variable Cells: $H$2:$H$11
Subject to the Constraints:
$H$2:$H$11<=$J$2
$H$2:$H$11=integer
$H$2:$H$11>=1
Select a Solving Method: Evolutionary

and click Solve. You'll have to change the $H$2:$H$11 range to be the full list of your managers. It'll run for a few minutes and come up with its best guess for a solution. Also keep in mind that the solution is often dependent on the initial conditions. So if you don't get an answer close enough to your liking, you can change the starting values in H to all 1's, or all 6's, and try again. You might get a better solution.

If you still can't get a good enough solution, there are a few other option. First, we can come up with a somewhat more complicated model which allows some departments to go to different groups, even though the manager is the same. We'd have to add a parameter that we also want to minimize so that happens as infrequently as possible.

Another option which may or may not work any better is we could write a macro that randomly assigns managers to groups, checks the "goodness" of the solution, and keeps the best. After a few thousand iterations, it might find something decent. But it may not. There over over 100 TRILLION options, and we just can't check them all.

There are also algorithms designed to do what you ask, like here:

https://www.geeksforgeeks.org/partition-set-k-subsets-equal-sum/

Unfortunately, I don't have time to adapt this algorithm for your situation. But there are possibilities. Good luck!