0

I have multiple lists ranking people. I have put them in an excel spreadsheet, each list occupying 1 column

A=washington Post
B=Yahoo
C=Fox
D=NFL.com

These lists are ranking the same individuals, so their names are on every list, but each list has them ranked differently. All the names are in the same format.

I want the names that appear highest on these lists most consistently to appear in order in a separate final column. The idea is rank the people that most sources rank highest most consistently.

So if there are 4 lists, and Joe Davidson is ranked 1,2,1,3 respectively, He would get a composite score of 7 (1+2+1+3). The person with the lowest composite score would appear highest in the final column, and the next lowest composite score would rank second, and so on down the column.

0

One possible approach, easy to maintain, using helper columns: Create a copy of the names in column E and sort them alphabetically.

Calculate the total ranking score in column F with

=MATCH(E2,$A$2:$A$21,0)+MATCH(E2,$B$2:$B$21,0)+MATCH(E2,$C$2:$C$21,0)+MATCH(E2,$D$2:$D$21,0)

Rank the results in column G with

=RANK(F2,$F$2:$F$21,1)

This will most likely include duplicates, so you need a tie break in column H using

=IF(COUNTIF($G$2:$G$21,G2)>1,(ROW()/100),0)

Add the tie break to the initial rank in column I

=G2+H2

Calculate the unique rank in column J

=RANK(I2,$I$2:$I$21,1)

List the names according to the unique rank with

=INDEX($E$2:$E$21,J2)

enter image description here

Hide columns E to J if they upset your spreadsheet layout or put them on a hidden sheet.

  • Everything Came out correctly except the final composite rank, all other columns worked correctly. Thanks for the response either. – user358807 Aug 18 '14 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.