## How to rank text uniquely in excel

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

**Answer:**

**Array formula in B2:**

Copy cell B2 and paste down as far as needed.

**Named ranges***
List *(A2:A13)

### How does this formula work?

Let us start with cell B2.

=COUNTIF(List, "<"&A2)+SUM(IF(A2=$A$2:A2, 1, 0))

*Step 1 - Calculate rank*

=COUNTIF(List, "<"&A2)

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.

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

Keep in mind, countif doesn´t rank uniquely if duplicate values are found.

SUM(IF(A2=$A$2:A2, 1, 0))

becomes

SUM(IF("DD"="DD", 1, 0))

becomes

SUM(1) and returns 1

*Step 3 - Add values*

Step 1 returned 7 and step 2 returned 1. 7 + 1 equals 8.

*Final comments*

Select B3 and use "evaluate formula" to see how the next cell calculates rank. Click "Evaluate" button and excel calculates cell formula step by step.

### Download excel example file.

text-values-uniquely-ranked.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

How to create an array of uniquely ranked duplicate values in excel

Question: How do I rank duplicate values uniquely also I need them in an array? Answer: Array formula in D2: […]You can click on headers to sort table for easy finding.

### 21 Responses to “How to rank text uniquely in excel”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 for download, 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?