# Assign to Groups based on Location/Manager

#### hmltnangel

##### Board Regular
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?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

##### Well-known Member
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?

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

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.

Replies
0
Views
49
Replies
3
Views
75
Replies
1
Views
88
Replies
6
Views
175
Replies
2
Views
64

1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

### 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.

### Which adblocker are you using?

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

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