Excel Sort Data by criteria

New Contributor

I have a list of employees that populates their rates.  I rank them by best rate.  I would like to be able to split the rank list to staff in different departments.   So if I have 10 people ranked but only need 2 in Receive department and 3 in ship department, It would split the ranking to give me best in both departments.  Receive would get rank #1 and #3.  Ship would get rank #2, #4, and #5. 




4 Replies

Hi @Skymaze 


at the moment, it's not completely clear to me what you would like to achieve.

Is it a continuous ranking only for the two relevant departments and ignoring the empty assignments? Then this could be a solution:





Or do you want to rank within the two relevant departmens? So e.g. rank #1 and #2 within Ship, and also rank#1 and #2 within Receive?



so the employees can be staffed in either department.  I want to be able to staff each area based upon need with the best employees (highest rates).  But I don't want to give one department the highest rates employees and leave the other department with lower rate employees.   I think of it as a total ranked pool that will be split between two areas based upon need.   The cells in gold is where I would input my need for each department.  Once inputed, the assignment column would calculate and show which employees to select and which department. 

best response confirmed by Skymaze (New Contributor)

Hi @Skymaze,


ok, then I completely misunderstood you at first. You want the asignment column to be calculated, not the rank column.


I found a relatively complex solution that should do the trick:





I'm sure some clever guys will find an easier solution, but for me it seems to work. Just give it a try.

Martin, absolutely perfect! I was on that track but just could not get it to work. Beautifully done sir!