## 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**

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 […]

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

### 2 Responses to “How to rank uniquely based on a condition”

### 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

I got the formula working, and although it's a great formula for criteria ranking it's not suitable for excel sheets with more than a thousand lines. Unless you have a very powerful machine.

This is exactly what I was looking for, except I want to give duplicate values the same ranking. In short I want to extract a unique list of names, by categories, however, the names show up multiple times. I don't want to use an array formula so I thought a shortcut would be to rank the unique people by each category then bring them into separate tables!