Assign to Groups based on Location/Manager

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
213
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Now, this is one I dont quite know how to describe right. SO bear with me :)

I have a table of 60 people who have been assigned a number (1-60) - This is called Table2

I have another table of 1100 people who need to be assigned to one of the 60 People in Table2. This large table is called Table1.

Ideally, the assignation of people should be to an individual in the same location (but this is not essential). And no-one should be in the same group as their manager.

I could strip data out and add a sample of the WB if necessary.

Table2 - assigned number is Column A (HPO Number)
Table 2 - Location is in Column D (Location)

Table 1 - Group Number will be in Column D (HPO Group)
Table 1 - Location is in Column BU (Work Location Name)
Table 1 - Manager is in Column AM (Manager Name)

any suggestions?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,400
Office Version
  1. 2016
Platform
  1. Windows
Hi hmltnangel,

Can I assume this is a one-off requirement (so I can give you some manual steps) or will you be doing this manually so need a fully automated solution?

Please advise:
  1. Location is the same as Work Location Name?
  2. How many different Locations are there?
  3. How many different Managers are there?
  4. How many Groups are there (or is that the 60 from Table2)?

Yes, it would help (and you're much more likely to receive a suggestion) if you can provide your data as XL2BB snippets.
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
213
Office Version
  1. 2016
Platform
  1. Windows
Thanks Toadstool

I will strip a version back and see if I can upload. Work blocking might stop me though. We'll see.

1. Yes
2. 30
3. 259
4. 53 (some groups ended up removed)

Where I am at with it.

I have created a formula in cell G1 which calculates the Max group size (currently 20) based on splitting the whole list out evenly across the 52/53 groups

=ROUNDUP(SUM(COUNTIFS(Table1[Champion],"")/COUNTIFS(Table1[Champion],"Yes")),0)

Then I used the Rand function to create a random number for everyone, Column F or Table Column "Rand"
Then Column E or "Group" works out with this formula and assigns everyone to a group not exceeeding the Max Group Size in the formula above. It also checks if the person is in the same group as their manager, if so reassigns them to another group.

=IF([@[Champion]]<>"","",IF(ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)=[Managers Group],ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)+1,ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)))

Then I am only stuck on the locations. How to make it check where the Champion is located and only assign people if the location matches the Champion.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,015
Messages
5,628,147
Members
416,295
Latest member
jjkh58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top