Allocation for employees

[AbhijeetChib]

I have the below data set where "Duplicate Groups" are in pairs are evenly distributed across primary resources. I need an output where one pair of duplicate group is mapped to only one Primary resource. 

Duplicate_Group	Primary Resource
207964	Monty
207964	Sam
207965	Shawn
207965	Kam
207966	Monty
207966	Sam
207967	Shawn
207967	Kam
207968	Monty
207968	Sam
207970	Kam
207970	Shawn
207971	Monty
207971	Sam
207972	Shawn
207972	Kam
207973	Monty
207973	Sam
207974	Shawn
207974	Kam
207975	Monty
207975	Sam
207976	Shawn
207976	Kam
207977	Monty
207977	Sam
207978	Shawn
207978	Kam
207979	Monty
207979	Sam
207980	Shawn
207980	Kam
207981	Monty
207981	Sam
207982	Shawn
207982	Kam
207983	Monty
207983	Sam
207984	Shawn
207984	Kam
207985	Monty
207985	Sam
207986	Shawn
207986	Kam
207987	Monty
207987	Sam
207988	Shawn
207988	Kam
207989	Sam
207989	Monty
207990	Shawn
207990	Kam
207991	Monty
207991	Sam
207992	Shawn
207992	Kam
207993	Monty
207993	Sam
207994	Shawn
207994	Kam
207995	Monty
207995	Sam
207996	Shawn
207996	Kam
207997	Monty
207997	Sam
207998	Shawn
207998	Kam
207999	Sam
207999	Monty
208000	Shawn
208000	Kam
208001	Monty
208001	Sam
208002	Shawn
208002	Kam
208003	Sam
208003	Monty
208004	Shawn
208004	Kam

In the above data set, the allocation for all 4 resources is equally distributed. 

Primary Assigned	Count
Sam	20
Kam	20
Shawn	20
Monty	20

However, if you see their Duplicate groups are different. For example in the below data set 207964 has Monty and Sam both. I need to either make it Sam or Monty.

207964	Monty
207964	Sam

Expected Output

Duplicate_Group	Primary Resource
207964	Monty	Monty
207964	Sam	Monty
207965	Shawn	Shawn
207965	Kam	Shawn
207966	Monty	Sam
207966	Sam	Sam
207967	Shawn	Kam
207967	Kam	Kam

Every pair of duplicate group needs to be mapped to any one resource, but the total allocation numbers needs to remain the same. 

[Tam]

@AbhijeetChib assuming there is no difference whether it's Sam or Monty gets assigned to 207964 the Unique tool and a join tool should do the trick.

HTH 

regards 

Tam 

[gawa · Accepted solution]

hi @AbhijeetChib 

Please try the attached WF if it fits to your real data or not.

[AbhijeetChib]

Thanks @Tam 

When i summarize your allocation this is what I get. 

Although the Duplicate Group are assigned perfectly but the count of allocation is not evenly distributed. I need to make them even. 

Kam - 10, Monty - 10, Sam - 10, Shawn -10 

Right_Primary Resource	CountDistinct_Duplicate_Group
Kam	1
Monty	17
Sam	3
Shawn	19

[Tam]

Thanks for pointing it out, I miss understood your ask.

Anyhow looks like @gawa solution worked. 

-r

Tam

[AbhijeetChib]

Hi @gawa 

If you remember this issue, where the allocation is distributed across 4 resources. How to make changes when the number of resource changes to 5, or can we make it dynamic ?