Assign to Groups based on Location/Manager

[hmltnangel]

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?

 

[Toadstool]

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]

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.