Return unique, distinct list in descending order, based on values in another column

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
162
Office Version
  1. 2019
Platform
  1. Windows
ABC
1NAMESCOREWANT THIS
2Cathy75Dan
3Bert50Cathy
4Cathy75Alan
5Bert50Bert
6Alan50
7Dan100

<tbody>
</tbody>

Hello all. I'm looking for a formula for C2 (copied down). The formula returns a unique distinct list (removes duplicates), in descending order of the person's score.
Notes:
  • Column A contains names, which may or may not have multiple entries in the column. The names listed don't follow any order.
  • The Score in column B is associated to the name in column A, and is always the same. So for example, Alan's score is always 50 in column B (whenever Alan's name shows up in column A).
  • It's possible 2 or more people have the exact same score.
  • It's also OK if the returned list is Dan, Cathy, Bert, Alan (as Alan and Bert have the same score)

Thanks!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Try this in C2:

Code:
=IFERROR(INDEX($A$2:$A$10,MATCH(LARGE(IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C1,$A$2:$A$10)=0,$B$2:$B$10)),1),IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C1,$A$2:$A$10)=0,$B$2:$B$10)),0)),"")

Confirm with Control+Shift+Enter, not just enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,987
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top