How can I get an equivalent of dense rank from SQL in Excel?

I have unsorted data (number of data varies from week to week) and the duplicates should have the same rank, i.e.

number rank
6 1
12 2
23 3
6 1
6 1

I have found this solution

=SUMPRODUCT( (FREQUENCY($A$1:$A$10, $A$1:$A$10) > 0) * (A1 >= $A$1:$A$11) )
Note the extra row required in the second expression. 

but I cannot use it as I do not know how many entries I will have and this one assumes I know the data set a priori.

2

2 Answers

Use:

INDEX(A:A,MATCH(1E+99,A:A))

To set the upper bounds of the range. This will find the last cell that has a number and set that as the last cell in the range. If the column has text instead then use "ZZZ" in place of the 1E+99.

For the add one we just add one to the MATCH:

INDEX(A:A,MATCH(1E+99,A:A)+1))

So the whole formula will be:

=SUMPRODUCT( (FREQUENCY($A$1:INDEX(A:A,MATCH(1E+99,A:A)), $A$1:INDEX(A:A,MATCH(1E+99,A:A))) > 0) * (A1 >= $A$1:INDEX(A:A,MATCH(1E+99,A:A)+1)) )

This is now dynamic, as values are added or removed from column A the reference will change accordingly.

You can use this formula:

=SUMPRODUCT((FREQUENCY(INDIRECT("$A$1:$A"&MAX((A:A<>"")*(ROW(A:A)))),INDIRECT("$A$1:$A"&MAX((A:A<>"")*(ROW(A:A)))))>0)*(A1>=INDIRECT("$A$1:$A"&(MAX((A:A<>"")*(ROW(A:A)))+1)))) 

Array Formula press Ctrl + Shift + Enter at the same time
MAX((A:A<>"")*(ROW(A:A))
Calculate the last cell in column A (gives the last non empty row in A)
Indirect will give the array reference (A1:A10) corresponding to last non empty row given by max
MAX((A:A<>"")*(ROW(A:A)))+1For the array A1:A11

I prefer you name the range A1:A10 define name Number for example
A1:A11 Numberplus

=SUMPRODUCT( (FREQUENCY(Number, Number) > 0) * (A1 >= Numberplus) )

In that case you change the range only in Name Manager for both names only, your formula will remain the same

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy