How to Count Duplicates sequentially with RANK function

[thenapolitan]

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

 

[Brian from Maui]

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

Adjust your ranges

 

[barry houdini]

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]

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]

It helped me too, thanks.

 

[jgonzaless]

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>