Binning Based on Multiple Criteria

[o-yasinakpinar]

Hi Everyone.

I've been struggling with this problem recently and I thought you could help me out. 

I have 2 lists (employee list and job list)  and I want to allocate each job in the job list to one of the employees. There are two conditions I would like to achieve simultaneously when allocating the jobs. 

Employee List

Employee Name

Employee 1

Employee 2

Employee 3

Employee 4

Job List

Job Code	Job Price
J1	100 $
J2	28 $
J3	34 $
J4	63 $
J5	88 $
J6	13 $
J7	184 $
J8	19 $
J9	53 $
J10	66 $
J11	111 $
J12	96 $
J13	81 $

Condition 1: I want each employee to have allocated similar number of jobs. I want to minimize the variance between number of jobs allocated to each employee.

Condition 2: I want each employee to have allocated similar amount of Price, I want to minimize the variance between Price allocated to each employee.

I can tile it by sum amount or equal records with Tile Tool, but I want both of these conditions to be met simultaneously. Should I try using Optimization Tool?

Thank you for your advices in advance. 

[Qiu]

@o-yasinakpinar 
I seems to be a Bin packing problem, but you also limited the number of bin, wihch is a bit tricky.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/HELP-How-to-use-ITERATIVE-MACRO/m-p/92...
I have given a try but honestly probaly not perfect.

[o-yasinakpinar]

Thank you for your solution. I appreciate it. Imagine Job List to contain tens of thousands of jobs and employee list hundreds of employees. Although it can be a shortcut solution, what I want is to find the most optimal solution not just any solution which satisfies the constraints. I believe it can be achieved by multi-objective optimization or goal programming algorithms. 

[Qiu · Accepted solution]

@o-yasinakpinar 
I thiink you are right.

Optimization is mostlike to be an iterative process and requires large dataset for calbrication.

Thank you for the feedback.