## How to rank text uniquely without duplicates

#### Table of Contents

## 1. How to rank text uniquely without duplicates

**Question:** How do I rank text cell values uniquely? If text values were sorted alphabetically from A to Z, the first text value would rank 1 and so on... Duplicate text values rank uniquely.

**Answer:**

**Formula in cell C3:**

Copy cell C3 and paste to cells below as far as needed.

### How does this formula work?

Let us start with cell C3.

=COUNTIF($B$3:$B$14,"<"&B3)+COUNTIF($B$3:B3,B3)

#### Step 1 - Calculate rank

The COUNTIF function is an incredibly versatile function, in this case, instead of counting values based on a condition we simply check if a value is smaller or larger than the others.

Remember, we are using text values so the function returns a rank number based on the position if the list were sorted alphabetically.

COUNTIF($B$3:$B$14,"<"&B3)

becomes

=COUNTIF({DD, AA, AA, BB, AA, DD, EE, AA, BB, EE, CC, EE}, "<"&DD)

and returns 7. 7 values are sorted before DD, as if they were sorted from A to Z.

The problem is that value DD has a duplicate and we need to make sure that the duplicate doesn't get the same number as the first instance.

#### Step 2 - Count current value and prior instances of current value

COUNTIF($B$3:B3,B3)

becomes

COUNTIF("DD","DD")

and returns 1.

Note that the cell reference $B$3:B3 expands as we copy the cell to cells below.

#### Step 3 - Add values

COUNTIF($B$3:$B$14,"<"&B3)+COUNTIF($B$3:B3,B3)

becomes

7 + 1 and returns 8 in cell C3.

#### Final notes

Select B3 and use "evaluate formula" to see how the next cell calculates rank. Press with left mouse button on "Evaluate" button and excel calculates cell formula step by step.

### Get *.xlsx file

Text values uniquely ranked.xlsx

## 2. How to rank uniquely based on a condition

The following formula ranks text values in column C uniquely based on the category in column B.

**Formula in D3:**

### Explaining formula in cell D3

#### Step 1 - Concatenate cell C3 and current row number

The ROW function returns the row number from a cell reference, if a cell ref is omitted then the row number of the current cell is returned.

The ampersand character & concatenates two values.

C3&ROW()

becomes

"parta"&3

and returns parta3.

#### Step - 2 Concatenate each cell in column C and corresponding row numbers row-wise

$C$3:$C$9&ROW($C$3:$C$9)

becomes

{"parta";"partb";"partb";"parta";"partc";"parta";"partb"}&{3;4;5;6;7;8;9}

and returns

{"parta3";"partb4";"partb5";"parta6";"partc7";"parta8";"partb9"}

#### Step 3 - Compare value with array using the larger than sign

(C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))

becomes

parta3>{"parta3";"partb4";"partb5";"parta6";"partc7";"parta8";"partb9"}

and returns

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

The number concatenated to each value makes it unique.

#### Step 4 - Compare categories with current cell

(B3=$B$3:$B$9)

becomes

7={7;7;7;11;11;7;11}

and returns

{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

#### Step - 5 Multiply arrays

(C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9)

becomes

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

and returns

{0;0;0;0;0;0;0}

#### Step 6 - Sum values in array

SUMPRODUCT((C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9))

becomes

SUMPRODUCT({0;0;0;0;0;0;0})

and returns 0.

#### Step 7 - Add 1

SUMPRODUCT((C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9)) + 1

becomes

0 + 1 equals 1 in cell D3.

**Get Excel *.xlsx file**

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

The following formula ranks text values in column C uniquely based on the category in column B. Formula in D3: […]

### 24 Responses to “How to rank text uniquely without duplicates”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

The formula works exactly as advertised and I have implemented it with no problems. However, even when using the "Evaluate Formula" feature to walk through the steps Excel uses to generate the output, I still don't understand how this formula works.

For instance, using the example file you provided, when I evaluate Cell B2 (which correctly ends up as 10), the final line before producing the output is:

MATCH(7.00000763673575,{6.00000763673575},0)

How is the MATCH function returning a 10 out of this? I sorta think I understand all the steps in the evaluation up to this point, but the money line eludes me. Would it be possible to break this formula down and explain the logic further.

If not, thanks for the formula anyway. I know how to use it even if I don't understand it.

And by Cell B2 I mean Cell B8, oops

Thank you for your comment!

Really good question, I have no answer why MATCH(7.00000763673575,{6.00000763673575},0) works.

I have changed the array formula in this post to a smaller, easier array formula. I also updated the attached excel file.

Oscar,

Thank you, the new formula works just as well and is obviously simpler.

But now you've piqued my interest. Where did the original come from? It seems like a rather complicated formula to have been derived by trial & error (although possible I guess), so somebody must have created it on purpose.

Thanks again.

If you use this formula, then you can enter it normally (that is, you do *not* have to use Ctrl+Shift+Enter, just Enter will work)...

=COUNTIF(A$2:A$20,"<"&A2)+COUNTIF($A$2:A2,A2)

If you use this formula, then you can enter it normally (that is, you do *not* have to use Ctrl+Shift+Enter, just Enter will work)...

=COUNTIF(List,"<"&A2)+COUNTIF($A$2:A2,A2)

Matt Gahlinger,

I created the original formula by trial and error. It is a weird formula that should not work, I think. But it it worked and then I forgot about it.

Rick Rothstein (MVP - Excel),

Thanks!

Guys,

All the formulas work as far as I can tell. Thanks for the multiple solutions.

Will the formula work if "List" (a named range) is substituted by A$2:A$13 instead?

I need to rank several text columns and would like to copy & paste the formulas & not have to define a name for each list.

Steve,

Yes, it will work.

Tried it & it worked perfectly. Thanks.

Tried it, and copied colums a and b to my excel, and it worked. But I could not copy and paste just the formula in my own excel, nor could I rewrite it without losing the unique part. I use Excel 2007

Brendon,

=COUNTIF(List, "<"&A2)+SUM(IF(A2=$A$2:A2, 1, 0)) You need to adjust cell references in both named range List and in formula.

My real data returned many duplicated rankings with the original formula. I used this to give unique rankings for each entry:

=COUNTIF(List, "<"&A2)+COUNTIF($A$2:A2,A2)

Just noticed that's effectively what "Rick Rothstein (MVP - Excel)" posted, so that makes two of us.

Hi Oscar,

I am trying to rank some scores for a golf sweepstake (without having to do it manually). I know how to organise the scoring into ascending order, but when I match the names to the scores (using vlookup), the players who are on the same score all come up with the same name. I then tried to apply your method, however, I can only get as far as the differentiating the top 4 players (all on 69). Is there anyway I can continue this ranking the whole way through down to the low of 79?

Score Player

73 Lee Westwood

70 Jim Furyk

76 Adam Scott

72 Hunter Mahan

77 Louis Oosthuizen

77 Rory McIlroy

78 Bubba Watson

70 Matt Kuchar

70 Ian Poulter

69 Graeme McDowell

69 Tiger Woods

76 Phil Mickleson

72 Jason Dufner

69 Nick Watney

74 Martin Kaymer

79 Luke Donald

69 Justin Rose

71 Francesco Molinari

77 Zach Johnson

75 Ernie Els

72 Rickie Fowler

75 Dustin Johnston

73 Sergio Garcia

73 Keegan Bradley

72 Nicolas Colsaerts

Jordy,

See attached file:

Jordy.xlsx

Thats fantastic Oscar! Looks far too complicated to have explained!

Cheers!

Jordy,

Formula in D2:

Array formula in E2:

Explaining array formula in cell E2Step 1 - Remove names already in listCOUNTIF($E$1:E1, $B$2:$B$26)

becomes

COUNTIF("", {"Lee Westwood"; "Jim Furyk"; "Adam Scott"; "Hunter Mahan"; "Louis Oosthuizen"; "Rory McIlroy"; "Bubba Watson"; "Matt Kuchar"; "Ian Poulter"; "Graeme McDowell"; "Tiger Woods"; "Phil Mickleson"; "Jason Dufner"; "Nick Watney"; "Martin Kaymer"; "Luke Donald"; "Justin Rose"; "Francesco Molinari"; "Zach Johnson"; "Ernie Els"; "Rickie Fowler"; "Dustin Johnston"; "Sergio Garcia"; "Keegan Bradley"; "Nicolas Colsaerts"})

and returns {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

Step 2 - Change False to True or True to False(0 is False and 1 is True)

NOT(COUNTIF($E$1:E1, $B$2:$B$26))

becomes

NOT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 3 - Multiply scores with array$A$2:$A$26*NOT(COUNTIF($E$1:E1, $B$2:$B$26))

becomes

{73; 70; 76; 72; 77; 77; 78; 70; 70; 69; 69; 76; 72; 69; 74; 79; 69; 71; 77; 75; 72; 75; 73; 73; 72}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{73; 70; 76; 72; 77; 77; 78; 70; 70; 69; 69; 76; 72; 69; 74; 79; 69; 71; 77; 75; 72; 75; 73; 73; 72}

Step 4 - Return the relative position of the scoreMATCH(D2, $A$2:$A$26*NOT(COUNTIF($E$1:E1, $B$2:$B$26)), 0)

becomes

MATCH(69, {73; 70; 76; 72; 77; 77; 78; 70; 70; 69; 69; 76; 72; 69; 74; 79; 69; 71; 77; 75; 72; 75; 73; 73; 72}, 0)

and returns 10

Step 5 - Return the value of a cell at the intersection of a particular row and column=INDEX($B$2:$B$26, MATCH(D2, $A$2:$A$26*NOT(COUNTIF($E$1:E1, $B$2:$B$26)), 0))

becomes

=INDEX({"Lee Westwood"; "Jim Furyk"; "Adam Scott"; "Hunter Mahan"; "Louis Oosthuizen"; "Rory McIlroy"; "Bubba Watson"; "Matt Kuchar"; "Ian Poulter"; "Graeme McDowell"; "Tiger Woods"; "Phil Mickleson"; "Jason Dufner"; "Nick Watney"; "Martin Kaymer"; "Luke Donald"; "Justin Rose"; "Francesco Molinari"; "Zach Johnson"; "Ernie Els"; "Rickie Fowler"; "Dustin Johnston"; "Sergio Garcia"; "Keegan Bradley"; "Nicolas Colsaerts"}, 10)

and returns Graeme McDowell in cell E2.

Hi Oscar,

That last explanation was fantastic, but I have another question now, probably slightly easier.

Sticking with golf, if there was a team game, taking a sum of the 3 best players from a team of 5.

However, the problem is if 3 out of 5 players miss the cut for the 3rd & 4th round the SMALL(F5:F9,{1,2,3}) doesn't work.

Is there some way I can add a formula that says something along the lines of, =if 3 or more out of 5 in F5:F9 say MC, then = MC at H10, if not then =(SUM(SMALL(F5:F9,{1,2,3})).

Hi Oscar, sorry to waste your time... I figured it out myself through trial and error...

It was quite simply

=IFERROR(SUM(SMALL(F5:F9,{1,2,3})),$J$24

J24 = N/A

New issue... J24 = OUT now... If more than one team is OUT, then the problem I had in the first place arises again!!! If there are 2 cells that say OUT, I can't get different names adjacent to the two cells that say OUT... Is there are simpler way to do this?

Hi, how can I apply this formula to multiple columns?

Hi Oscar. I tried this formula. For some reason, the numbers were off by 8, so I subtracted 8 at the end of the formula and it now ranks correctly.

But I have several columns that I want to ranked simultaneously. How can I use this formula with multiple columns? Going with your sample data for example, there is List 1 column, List 2 column, and List 3 column. List 2 is a sub-category to List 1. List 3 is a subcategory of List 2.

Hi Oscar, How I use this formula to rank text with multiple criteria-columns?