# Rank list sequentially without skipping rank value

1. ## Rank list sequentially without skipping rank value

I need a formula to rank my values so that duplicate values receive the same rank and subsequent values are ranked sequentially without skipping rank.

RANK.EQ skips rank value, and the temporary fix that I have used (below) is not sortable.
Formula:
`Please Login or Register  to view this content.`

Could this be solved with some use of FREQUENCY?

2. ## Re: Rank list sequentially without skipping rank value

Try this ...

=SUMPRODUCT((\$A\$2:\$A\$45<=A2)/COUNTIF(\$A\$2:\$A\$45,\$A\$2:\$A\$45))

3. ## Re: Rank list sequentially without skipping rank value

Try this:

=SUMPRODUCT((A2 > A\$2:A\$45)/COUNTIF(A\$2:A\$45,A\$2:A\$45))+1

4. ## Re: Rank list sequentially without skipping rank value

Great! That does what I need. Thank you both.

5. ## Re: Rank list sequentially without skipping rank value

One more question:
I'm going to set this up in a template to sort material lists. Some of the lists have thousands of entries, whereas others only have a hundred or so. Is there a way to adjust the endpoint of the array so that it automatically detects where the list ends instead of me having to manually manipulate it?

For example:
For the first list I am sorting to A45, but the second list only goes to A20. If I import the 19 item list into the table I get a #VALUE error unless I adjust the formula to read
Formula:
`Please Login or Register  to view this content.`

Is there a way to set it up for 4,000 entries (\$A\$4001) but have it autodetect if the list does not extend that far?

6. ## Re: Rank list sequentially without skipping rank value

Originally Posted by Phuocam
Try this ...

=SUMPRODUCT((\$A\$2:\$A\$45<=A2)/COUNTIF(\$A\$2:\$A\$45,\$A\$2:\$A\$45))
Phuocam,
I also had to make one more adjustment to the formula so that VLOOKUP could accurately read the numbers. Many of the entries round to #.9999... within the SUMPRODUCT calculation, so I added ROUNDUP.
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Rank list sequentially without skipping rank value

In the sheet you posted, there was a formula in A47. I assume that it is not present in your real data. here is a way, using a Named Range - CTRL-F3 to view - to make the column range fully dynamic. It's called data, and the formula becomes:

=SUMPRODUCT((A2 >data)/COUNTIF(data,data))+1

There is something strange about your last comment, concerning ROUNDUP. The formula returns INTEGERS. Can you explain where the VLOOKUP and problems arise? The problem lies elsewhere....

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

8. ## Re: Rank list sequentially without skipping rank value

Glenn,

The named range solution works great. Thank you!

For the VLOOKUP issue, I've started a new thread here.

9. ## Re: Rank list sequentially without skipping rank value

You're welcome.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

10. ## Re: Rank list sequentially without skipping rank value

Originally Posted by Glenn Kennedy
Try this:

=SUMPRODUCT((A2 > A\$2:A\$45)/COUNTIF(A\$2:A\$45,A\$2:A\$45))+1
Thanks, this works for me in some cases. However, if I want to do this with an if condition, i.e. for different categories (like I before used to do with countifs statements) it doesn't work. Could anyone help me with this?
Capture.PNG

Cheers,

Jelco

11. ## Re: Rank list sequentially without skipping rank value

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

12. ## Re: Rank list sequentially without skipping rank value

Hi, could you help to explain how this formula works?
Thanks so much for your help!

13. ## Re: Rank list sequentially without skipping rank value

Hello Glenn, could you help to explain the logic of this formula?
Thank you so much,

14. ## Re: Rank list sequentially without skipping rank value

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)