RANKIF

Post date: Jan 5, 2016 11:04:25 PM

A forum poster asked: "I have a large spreadsheet that has education levels for our employees. Many employees ha

ve more than one degree. I want to just return the highest level for each employee. Nothing I've looked at will do what I want. Manually deleting lower degree rows is time consuming."

Wouldn't a RANKIF function be nice? We could rank order the degrees for each employee, hide everything except the highest ranking degrees, then delete everything visible. Unfortunately, there's no RANKIF function. But we can make one.. Here's how:

Step 1: Give a numeric value to each degree designating their level

At right is a simple table listing possible degrees. Assign each degree a score to indicate which is higher. Bigger numbers indicate higher degrees. Make sure our data is a table (use CTRL-T).

Step 2: Rank order the degrees for each employee

1. Add a helper column to our employee table with the heading Score:
2. Enter this formula in the Score column
3. =IFNA(VLOOKUP([@Degree],tblDegrees,2,FALSE),0)
4. Copy down all rows
5. Add another helper column with the heading Rank

Enter this formula the Rank column's first row
=1+SUMPRODUCT(([Name]=[@Name])*([Score]>[@Score]))
Copy down all rows

Explanation:

([Name]=[@Name]) Compares the entire list of employee names to the current row's employee name. Where the name matches, the result is TRUE. Where it does not match, the RESULT is FALSE; thus, if there are 1000 names we will get an array of 1000 TRUE and FALSE values.

([Score]>[@Score]) Compares the entire list of Scores to the current row's score. Where score column is greater than the current row's score the result is TRUE; otherwise, FALSE.

([Name]=[@Name])*([Score]>[@Score]) The next bit is a trick as well. If we multiple TRUE and/or FALSE with anything, TRUE becomes 1 and FALSE becomes 0.

SUMPRODUCT Sums the results of our multiplications

Step 3: Delete unwanted records

This part is easy - but first - save your work. Now select any cell in our data and press SHIFT-CTRL-L. This turns on the autofilter. Deselect "1" from the Rank column's Autofilter so all lower degrees are visible. Select all visible rows and delete them. Click on any of the list's column heading and press SHIFT-CTRL-L to bring our higher degrees into view.

Keep XL'n!

Example: https://www.dropbox.com/s/wthxq43448btqza/RANKIF.xlsx?dl=0

Page updated

Google Sites

Report abuse