Juggling between allocations with logic

[AbhijeetChib]

Good Day Everyone ! 

I have this very specific requirement, I will try to explain from the excel data below. 

Country	Primary	Primary_Resource
Austria	SAM	Mani
Austria	SAM	Mani
Austria	SAM	Kev
Austria	SAM	Kev
Austria	SAM	Mani
Austria	SAM	Mani
Austria	SAM	Sid
Austria	SAM	Sid
Austria	SAM	Mani
Austria	SAM	Mani
Russian Federation	Sid	Sid
Russian Federation	Sid	Sid
Russian Federation	Sid	SAM
Russian Federation	Sid	SAM
Austria	SAM	Kev
Austria	SAM	Kev
Bangladesh	Mani	SAM
Bangladesh	Mani	SAM
Slovenia	Kev	Sid
Slovenia	Kev	Sid
Austria	SAM	SAM
Austria	SAM	SAM
Bangladesh	Mani	Sid
Bangladesh	Mani	Sid
Bangladesh	Mani	Mani
Bangladesh	Mani	Mani
Bangladesh	Mani	Sid
Bangladesh	Mani	Sid
Bangladesh	Mani	Mani
Bangladesh	Mani	Mani
Bangladesh	Mani	Sid
Bangladesh	Mani	Sid
Poland	SAM	SAM
Poland	SAM	SAM
USA	Sid	Sid
USA	Sid	Sid
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Kev
USA	Sid	Kev
Switzerland	Mani	SAM
Switzerland	Mani	SAM
Mexico	Kev	Sid
Mexico	Kev	Sid
Russian Federation	Sid	SAM
Russian Federation	Sid	SAM
Russian Federation	Sid	Sid
Russian Federation	Sid	Sid
Switzerland	Mani	SAM
Switzerland	Mani	SAM
Mexico	Kev	Kev
Mexico	Kev	Kev
South Africa	SAM	Mani
South Africa	SAM	Mani
Malaysia	SAM	Sid
Malaysia	SAM	Sid
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
India	Kev	Mani
India	Kev	Mani
USA	Sid	Kev
USA	Sid	Kev
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Kev
USA	Sid	Kev
Thailand	SAM	SAM
Thailand	SAM	SAM
Mexico	Kev	Sid
Mexico	Kev	Sid
Mexico	Kev	Mani
Mexico	Kev	Mani
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Mexico	Kev	SAM
Mexico	Kev	SAM
USA	Sid	Sid
USA	Sid	Sid
Russian Federation	Sid	SAM
Russian Federation	Sid	SAM
USA	Sid	Sid
USA	Sid	Sid
Germany	SAM	Mani
Germany	SAM	Mani
USA	Sid	Sid
USA	Sid	Sid
Switzerland	Mani	Mani
Switzerland	Mani	Mani
Taiwan	SAM	Kev
Taiwan	SAM	Kev
Russian Federation	Sid	Mani
Russian Federation	Sid	Mani
USA	Sid	Kev
USA	Sid	Kev
Japan	Kev	Mani
Japan	Kev	Mani
Japan	Kev	Kev
Japan	Kev	Kev
Japan	Kev	SAM
Japan	Kev	SAM
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Mexico	Kev	SAM
Mexico	Kev	SAM
Pakistan	SAM	Sid
Pakistan	SAM	Sid
Austria	SAM	Mani
Austria	SAM	Mani
Austria	SAM	Sid
Austria	SAM	Sid
Austria	SAM	Mani
Austria	SAM	Mani
Austria	SAM	Kev
Austria	SAM	Kev
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
USA	Sid	SAM
USA	Sid	SAM
USA	Sid	Kev
USA	Sid	Kev
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
USA	Sid	SAM
USA	Sid	SAM
USA	Sid	Sid
USA	Sid	Sid
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
USA	Sid	SAM
USA	Sid	SAM
USA	Sid	Kev
USA	Sid	Kev
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Kev
USA	Sid	Kev
USA	Sid	SAM
USA	Sid	SAM
Germany	SAM	Kev
Germany	SAM	Kev
Germany	SAM	Mani
Germany	SAM	Mani
Ireland	Kev	Sid
Ireland	Kev	Sid
USA	Sid	Mani
USA	Sid	Mani
Canada	Kev	Kev
Canada	Kev	Kev
USA	Sid	SAM
USA	Sid	SAM
Switzerland	Mani	Kev
Switzerland	Mani	Kev
United Kingdom	Kev	Mani
United Kingdom	Kev	Mani
USA	Sid	Kev
USA	Sid	Kev
Saudi Arabia	SAM	Mani
Saudi Arabia	SAM	Mani
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Switzerland	Mani	SAM
Switzerland	Mani	SAM
India	Kev	Sid
India	Kev	Sid
Switzerland	Mani	Mani
Switzerland	Mani	Mani
Ireland	Kev	Sid
Ireland	Kev	Sid
USA	Sid	Mani
USA	Sid	Mani
Switzerland	Mani	Kev
Switzerland	Mani	Kev
Switzerland	Mani	SAM
Switzerland	Mani	SAM
China	SAM	Sid
China	SAM	Sid
USA	Sid	Mani
USA	Sid	Mani
Switzerland	Mani	Kev
Switzerland	Mani	Kev
Switzerland	Mani	Mani
Switzerland	Mani	Mani
USA	Sid	Kev
USA	Sid	Kev
Switzerland	Mani	SAM
Switzerland	Mani	SAM
China	SAM	Kev
China	SAM	Kev
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
Switzerland	Mani	Mani
Switzerland	Mani	Mani
Belgium	Mani	Kev
Belgium	Mani	Kev
USA	Sid	SAM
USA	Sid	SAM
USA	Sid	Kev
USA	Sid	Kev
United Kingdom	Kev	Mani
United Kingdom	Kev	Mani
Switzerland	Mani	Kev
Switzerland	Mani	Kev
USA	Sid	SAM
USA	Sid	SAM
Switzerland	Mani	Kev
Switzerland	Mani	Kev
USA	Sid	SAM
USA	Sid	SAM
Switzerland	Mani	Kev
Switzerland	Mani	Kev
USA	Sid	SAM
USA	Sid	SAM
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Switzerland	Mani	SAM
Switzerland	Mani	SAM
USA	Sid	Kev
USA	Sid	Kev
USA	Sid	SAM
USA	Sid	SAM
Italy	Kev	Kev
Italy	Kev	Kev
USA	Sid	SAM
USA	Sid	SAM
USA	Sid	Sid
USA	Sid	Sid
Switzerland	Mani	SAM
Switzerland	Mani	SAM
Switzerland	Mani	Sid
Switzerland	Mani	Sid
USA	Sid	Mani
USA	Sid	Mani
Switzerland	Mani	Kev
Switzerland	Mani	Kev
USA	Sid	Mani
USA	Sid	Mani
Ireland	Kev	Kev
Ireland	Kev	Kev
USA	Sid	SAM
USA	Sid	SAM
China	SAM	Kev
China	SAM	Kev
Australia	Kev	SAM
Australia	Kev	SAM
Australia	Kev	Sid
Australia	Kev	Sid
Australia	Kev	SAM
Australia	Kev	SAM
Australia	Kev	Sid
Australia	Kev	Sid
Japan	Kev	SAM
Japan	Kev	SAM
China	SAM	Sid
China	SAM	Sid
USA	Sid	Mani
USA	Sid	Mani
USA	Sid	Sid
USA	Sid	Sid
Switzerland	Mani	SAM
Switzerland	Mani	SAM
Switzerland	Mani	Kev
Switzerland	Mani	Kev
Austria	SAM	SAM
Austria	SAM	SAM
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Russian Federation	Sid	Mani
Russian Federation	Sid	Mani
Switzerland	Mani	Sid
Switzerland	Mani	Sid
Vietnam	SAM	Mani
Vietnam	SAM	Mani
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Vietnam	SAM	SAM
Vietnam	SAM	SAM
Germany	SAM	Kev
Germany	SAM	Kev
Vietnam	SAM	Mani
Vietnam	SAM	Mani
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Vietnam	SAM	SAM
Vietnam	SAM	SAM
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Vietnam	SAM	SAM
Vietnam	SAM	SAM
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Vietnam	SAM	SAM
Vietnam	SAM	SAM
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Vietnam	SAM	SAM
Vietnam	SAM	SAM
Vietnam	SAM	Kev
Vietnam	SAM	Kev
Japan	Kev	Mani
Japan	Kev	Mani
Switzerland	Mani	Kev
USA	Mani	Kev
Switzerland	Mani	SAM
Switzerland	Mani	SAM
United Kingdom	Kev	Sid
United Kingdom	Kev	Sid
Malaysia	SAM	Mani
United Kingdom	SAM	Mani
Slovenia	Kev	Kev
Italy	Kev	Kev
India	Kev	SAM
India	Kev	SAM
Spain	Sid	Sid
USA	Sid	Sid
France	SAM	Mani
France	SAM	Mani

Field primary has jobs assigned to resources based on their countries. Field Primary_Resource has all the job distributed equally amongst the resources (SAM, KEV, MANI and SID). The requirement is that total number of jobs needs to be divided by 4(number of resources) and a threshold number needs to be established. In this case its 326/4 = 81(rounded off in this case). So every Primary above the threshold needs to allocated to Primary_Resource maintaining their countries. For example SID has 115 in Primary so 81 of them needs to remain to SID and remaining needs to be allocated to others. All of SID's locations needs to remain with SID till the threshold of 81 and the remaining can be distributed to others. 

Similarly SAM has 85, 81 of them needs to remain with SAM and 4 needs to be distributed. 81 of SAM's countries need to remain with SAM and remaining can be distributed to KEV and MANI

In the same way - Kev has 57, he will need to receive 24 from others but needs to keep his 57 with him. 

I hope I am making sense. 

[DavidSkaife]

Hi @AbhijeetChib 

While it doesn't look quite the same as your output, how does this work for you?

[AbhijeetChib]

@DavidSkaife  Sorry i think I did not make myself clear. Edited the test a bit, please take a look