Excel Sort Data by criteria

[Skymaze]

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. 

[#2]

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:

=IF(E9<>"",RANK($C9,$C$9:$C$17,1)+COUNTIF(C$9:C9,C9)-1,"")

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?

[#3]

@Martin_Weiss 

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. 

[#4 · Accepted solution]

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:

=IF(OR(
AND(ISODD(D9),(D9/2)<=$C$5,D9<=($C$5+$C$6)),
AND(ISEVEN(D9),(D9/2)>$C$6,D9<=($C$5+$C$6))),"Receive",
IF(OR(
AND(ISEVEN(D9),(D9/2)<=$C$6,D9<=($C$5+$C$6)),
AND(ISODD(D9),(D9/2)>$C$5,D9<=($C$5+$C$6))),"Ship",""))

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

[#5]

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