SOLVED

New Contributor

# 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.

4 Replies

# Re: Excel Sort Data by criteria

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?

# Re: Excel Sort Data by criteria

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)
Solution

# Re: Excel Sort Data by criteria

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)),