Rank list sequentially without skipping rank value

[theTaoJones]

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.

Please Login or Register  to view this content.

Could this be solved with some use of FREQUENCY?

Please see attached sheet.

[Phuocam]

Try this ...

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

[Glenn Kennedy]

Try this:

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

[theTaoJones]

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

[theTaoJones]

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

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?

[theTaoJones]

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.

Please Login or Register  to view this content.

[Glenn Kennedy]

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

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

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.

[theTaoJones]

Glenn,

The named range solution works great. Thank you!

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

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

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.

[jelco]

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

[AliGW]

Administrative Note:

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.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

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

[Chau Vu]

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

[Chau Vu]

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

[AliGW]

Administrative Note:

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.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

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