Rank : how to prevent duplicates.


How do I fix so I do not have duplicates.

thank you for any help anyone provides

What duplicates? We don’t know your data, we don’t know your results. RANK() just determines the rank of a value within a given set of data.

If you have duplicates / repetitions, you have duplicates. No fix insofar.
The exactly same value occurring more than once in a sequence of numbers has the same rank on every occurrence. the higher ranks are omitted as often as a repetition occurs and the next number to be ranked in the chosen order gets the first rank number unused so far. That’s the way ranking is done everywhere.
If you need disambiguation, you need to specify in what way. It cannot be done for sets.

Disambiguation of ranks always requires to introduce a “second key of order”.
If the numbers to rank are taken from a cell range which is part of a single column e.g. you can use the row number for disambiguation in many cases. You will have to assure something about the numeric range and the range of row numbers as well to allow for a reliable solution.

I am not speaking to @Lupp’s question of stability here, but the following video from Leila Gharani outlines a simple approach used for maintaining duplicate ranked values when building a chart.


1 Like

Adapted to the example attached to my suggested solution below the formula would read
and this shows a really good idea which should be more efficient and much less (if at all) depending on details.
(To present it as an image is a very bad idea. And the semicolon as parameter separator in Calc should always be preferred over the comma to ease trans-locale cooperation.)
I will also edit my answer below to include the better idea.

My take was that a video would be ideal for the OP. The image is taken from that video. Next time I’ll do both or, better yet, attach an example ODS. :slightly_smiling_face:

1 Like

Excuse me for sounding like a schoolmaster.


Perhaps it’s what you are looking for.


[My attention was drawn again to this old topic by a recent post. Since I think that both the solutions already suggested here are not suitable, I now also post a solution including explanations. Since duplicates cant’ be “prevented” if they simply are factual, my solution is based on the assumption that multiply occurring ranks shall be avoided or probably better: disambiguated.]

Anybody feeling tempted to use the given solutions by formulas for sorting should withstand!

See attached spreadsheet document:
ask35784disambiguatedRanking.ods (31.2 KB)

Much better (see comments above):
ask35784disambiguatedRankingB.ods (31.1 KB)