Formula to split employees into teams of 3 that are the same region BUT don't include the respective manager

[maxim642]

I'd like a way to split the below data into groups of 3, so x13 groups.

The groups should ideally comprise 3 employees from the same region (all from Asia, or all from Europe), but if this can't be achieved because of the numbers involved it's not a dealbreaker.

The most important criteria is that each group should NOT include any manager for any of its respective members, e.g. if group 1 had Joe Bloggs 1, Joe Bloggs 2 & Joe Bloggs 3, managers 3, 39 and 2 could not be in that group.

Is there any way of doing this apart from manually going through the list and eyeballing each line? I thought a pivot might work but then got stuck there too. Thanks.

Employee	Job Title	Level	Department	Directorate	No.of Reports	Country	Region	Manager	Group
Joe Bloggs 1	x	x	x	x	x	x	Asia	Joe Bloggs 3
Joe Bloggs 2	x	x	x	x	x	x	Asia	Joe Bloggs 39
Joe Bloggs 3	x	x	x	x	x	x	Asia	Joe Bloggs 2
Joe Bloggs 4	x	x	x	x	x	x	Asia	Joe Bloggs 1
Joe Bloggs 5	x	x	x	x	x	x	Asia	Joe Bloggs 2
Joe Bloggs 6	x	x	x	x	x	x	Europe	Joe Bloggs 27
Joe Bloggs 7	x	x	x	x	x	x	Europe	Joe Bloggs 8
Joe Bloggs 8	x	x	x	x	x	x	Asia	Joe Bloggs 18
Joe Bloggs 9	x	x	x	x	x	x	Asia	Joe Bloggs 2
Joe Bloggs 10	x	x	x	x	x	x	Europe	Joe Bloggs 7
Joe Bloggs 11	x	x	x	x	x	x	Europe	Joe Bloggs 2
Joe Bloggs 12	x	x	x	x	x	x	Europe	Joe Bloggs 25
Joe Bloggs 13	x	x	x	x	x	x	Europe	Joe Bloggs 2
Joe Bloggs 14	x	x	x	x	x	x	Europe	Joe Bloggs 25
Joe Bloggs 15	x	x	x	x	x	x	Asia	Joe Bloggs 13
Joe Bloggs 16	x	x	x	x	x	x	Europe	Joe Bloggs 2
Joe Bloggs 17	x	x	x	x	x	x	Asia	Joe Bloggs 1
Joe Bloggs 18	x	x	x	x	x	x	Europe	Joe Bloggs 20
Joe Bloggs 19	x	x	x	x	x	x	Europe	Joe Bloggs 17
Joe Bloggs 20	x	x	x	x	x	x	Europe	Joe Bloggs 11
Joe Bloggs 21	x	x	x	x	x	x	Europe	Joe Bloggs 16
Joe Bloggs 22	x	x	x	x	x	x	Europe	Joe Bloggs 2
Joe Bloggs 23	x	x	x	x	x	x	Asia	Joe Bloggs 19
Joe Bloggs 24	x	x	x	x	x	x	Europe	Joe Bloggs 2
Joe Bloggs 25	x	x	x	x	x	x	Europe	Joe Bloggs 27
Joe Bloggs 26	x	x	x	x	x	x	Asia	Joe Bloggs 20
Joe Bloggs 27	x	x	x	x	x	x	Europe	Joe Bloggs 12
Joe Bloggs 28	x	x	x	x	x	x	Europe	Joe Bloggs 23
Joe Bloggs 29	x	x	x	x	x	x	Asia	Joe Bloggs 7
Joe Bloggs 30	x	x	x	x	x	x	Europe	Joe Bloggs 9
Joe Bloggs 31	x	x	x	x	x	x	Europe	Joe Bloggs 5
Joe Bloggs 32	x	x	x	x	x	x	Europe	Joe Bloggs 6
Joe Bloggs 33	x	x	x	x	x	x	Europe	Joe Bloggs 3
Joe Bloggs 34	x	x	x	x	x	x	Europe	Joe Bloggs 21
Joe Bloggs 35	x	x	x	x	x	x	Europe	Joe Bloggs 39
Joe Bloggs 36	x	x	x	x	x	x	Europe	Joe Bloggs 37
Joe Bloggs 37	x	x	x	x	x	x	Europe	Joe Bloggs 21
Joe Bloggs 38	x	x	x	x	x	x	Europe	Joe Bloggs 22
Joe Bloggs 39	x	x	x	x	x	x	Asia	Joe Bloggs 20

 

[maxim642]

Solved

 

[smozgur]

Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.

Otherwise, please do not mark a post that doesn't contain a solution.