# How to Count Duplicates sequentially with RANK function

#### thenapolitan

##### Board Regular
Hi All,

I understand the idea of using something like this where Column C = RANK(A1,\$A\$1:\$A\$8,1) & Column D =RANK(A1,\$A\$1:\$A\$8,1)+COUNTIF(\$A\$1:A1,A1)-1

But what I really want is to order the rank data without skipping a number, such as Column E.

 ColumnA ColumnB ColumnC ColumnD ColumnE 2 2 1 1 1 3 * 2 2 2 3 1 2 3 2 4 5 4 4 3 5 5 5 5 4 6 5 6 6 5 7 5 7 7 6 8 5 8 8 7

<colgroup><col span="5"></colgroup><tbody>
</tbody>

Any ideas on how to do this?

Thanks,

Chris

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Brian from Maui

##### MrExcel MVP
In column E, try

=IF(ISNUMBER(MATCH(B2,\$B\$1:B1,0)),VLOOKUP(B2,\$B\$1:C1,2,0),RANK(C2,\$C\$2:\$C\$5,1))

Where Column B houses numbers to rank

Where column C house

=IF(ISNUMBER(MATCH(B2,\$B\$1:B1,0)),"",RANK(B2,\$B\$1:\$B\$5))

#### barry houdini

##### MrExcel MVP
You could use this "array formula" in E1

<a1,a\$1:a\$8),a\$1:a\$8),1))+1
=SUM(IF(FREQUENCY(IF(A\$1:A\$8< A1,A\$1:A\$8),A\$1:A\$8),1))+1

confirm with CTRL+SHIFT+ENTER and copy down</a1,a\$1:a\$8),a\$1:a\$8),1))+1

#### thenapolitan

##### Board Regular
Ended up using the "array formula" version of this and it works great! Thanks so much barry.

You could use this "array formula" in E1

<a1,a\$1:a\$8),a\$1:a\$8),1))+1
=SUM(IF(FREQUENCY(IF(A\$1:A\$8< A1,A\$1:A\$8),A\$1:A\$8),1))+1

confirm with CTRL+SHIFT+ENTER and copy down
</a1,a\$1:a\$8),a\$1:a\$8),1))+1

#### gorgoszka

##### New Member
It helped me too, thanks.

#### jgonzaless

##### New Member
I used this formula in B3 after filling the initial rank for B2.

<tbody>
</tbody>
=IF(A3=A2,B2,B2+1)

 Data Rank 10 1 10 1 9 2

<tbody>
</tbody>

Replies
4
Views
218
Replies
7
Views
159
Replies
4
Views
170
Replies
2
Views
217
Replies
3
Views
76

1,195,719
Messages
6,011,290
Members
441,599
Latest member
Jribas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back