Forum Discussion
Excel Sort Data by criteria
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.
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.
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:
=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?
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.
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.
