CODEWITHSUNDEEP
excelranking
Jennifer Farnham
Jennifer Farnham

Reputation: 1

Ranking in Excel

I need help with an Excel formula. I have a spreadsheet built with people's names and dates.
I am tracking days they volunteer to pick up extra shifts. They get a number of points for different shifts volunteered for and the date they volunteered for.
I need to rank/sort the list of people by points and then by most recent date volunteered for.
Does that make sense?
I can't figure out how to write a formula to move the people without manually cutting and pasting.

Upvotes: 0

Views: 219

Answers (1)

micstr
micstr

Reputation: 5206

Look at RANK() formula.

I suggest Rank the people, then on another sheet use a vlookup to get a list of top people (Put rank on far left of people names to help your clookup)

Your data range

 Rank people hours/or points
    3 Bob 1  
    1 Cat 10 
    2 Dave 3

Where formula in Rank columns is (if Rank is A1) 3 in A2 will be = RANK(C2,$C$2:$C$4) pointing to 1 the value of hours, or points, you have made up.

Then, you list the numbers you have of people and then vlookup on that number

 RankNum  Top helpers Their hours
    1  =vlookup(A2 i.e. 1 on left, rangeofdata,2,false) =vlookup(A2, rangeofdata,3,false)
    2  ...

will give you on your output sheet

1 Cat 10
2 Dave 3 
3 Bob 1

And will dynamically change as you update data. You may need some tricks if hours are equal though as vlookup looks for uniques.

This duplicates data but keeps your reporting separate from your data.

SORTING Other suggestions will be sorting the existing data, but I like to keep my reporting e.g. Top 10 helpers or Ranking sheets away from my data.

Upvotes: 1

Related Questions