Group list of names while considering different dependencies

[Seba Robles]

So here's an interesting challenge I've been trying to work on... I have the idea in my head but can't seem to translate it into Excel.

I have a long list of names that I'm trying to group together into different waves. These waves have different logical conditions (ordered by priority);

1. People under the same 'Dependency' should be within the same 'Group'
2. Additionally, 'Secretaries' should be grouped with their respective leads (E.g. Daniela should be in Sarah's group) which takes precedence over the 'Dependency' if Daniela is in a different 'Dependency' in this specific case
3. If none of the above are met, people should be grouped by 'Department' (this can be by alphabetical order A to Z)
4. A group should stay within 900 to 1,200 people. If the max is reached, a new group is created. (Different 'Dependencies' can be within the same group as long as it's within the range)

Name	Dependency	Secretary	Department	Group
Daniela	No Dependency	-	Dept. A	1
Jack	Group A	-	Dept. A	1
Sarah	Group B	Daniela	Dept. B	2
John	Group C	Bob	Dept. B	2
Katie	Group C	-	Dept. C	2
Sam	Group A	-	Dept. C	1
Bob	No Dependency	-	Dept. D	2
Horace	No Dependency	-	Dept. E	3

I'm trying to find a quick way to get through a list that has thousands of rows and many more columns, but those are the ones I'm looking at... if it's not possible I'd have to do it by brute force.

Let me know if you have any questions or need me to clarify anything.

Any helps is appreciated!!

 

[Seba Robles]

Hello! Wanted to see if anyone could help with the above?