0

I want to generate Rank similar function works like DENSE_RANK() in database product (such as SQL Server).

Here is sample data :

Col1 
1
2
3
4
4 

So, i want to be generate following sequence.

Col1  sequence
1      4
2      3
3      2 
4      1 
4      1

As of now i have use rank() function. But, it doesn't work as i expect.

=RANK(A1,A1:A5, 0)

1 Answer 1

1

If one has the Dynamic Array formula UNIQUE:

=SUMPRODUCT(--(A1<=UNIQUE(A:A)))

enter image description here


If not we can create the unique list with this:

INDEX(A:A,N(IF({1},MODE.MULT(IF(MATCH($A$1:$A$5,A:A,0)=ROW($A$1:$A$5),ROW($A$1:$A$5)*{1,1})))))

this makes the whole formula an array formula and must be confirmed with Ctrl-Shift-enter when exiting edit mode.

=SUMPRODUCT(--(A1<=INDEX(A:A,N(IF({1},MODE.MULT(IF(MATCH($A$1:$A$5,A:A,0)=ROW($A$1:$A$5),ROW($A$1:$A$5)*{1,1})))))))

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .