Help with the rank function

[Victor Delta]

I have a column of figures obtained by using the rank function. However, as
usual, they contain duplicates where appropriate eg 3 ones, followed by a
four etc.

I want to convert the list into a unique set of numbers without any
duplicates - and it doesn't matter which order the 3 ones are in but they
need to change to 1, 2, 3 etc

Can anyone please suggest a way of doing this.

TIA

V

 

[Luke M]

Assume your list is in A2:10. Formula in B2 then is:

=RANK(A2,$A$2:$A$10)+COUNTIF(A3:$A$11,A2)

Copy down as needed.
Adjust references and range size as needed, but note that the COUNTIF
function is offset 1 row.

 

[Mike H]

Hi,

I don't understand, for this set of data what do you expect to see as an
answer?

1
2
3
3
3
4
5
5
6

Mike


I have a column of figures obtained by using the rank function. However, as

 

[Pete_UK]

Assuming your numbers are in A1:A10, put this in B1:

=RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1

and copy down. Adjust the A$10 term if you have more numbers.

Hope this helps.

Pete

 

[Bernd P]

Hello,

A general solution which works for numbers as well as for texts:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Victor Delta]

Hello,

A general solution which works for numbers as well as for texts:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

Many thanks to all for the suggested solutions.

V

 

[dhstein]

You can also add a small random number to each value -- "Your Number" +
(Rand() /100000). As long as the denominator is large enough it should have
no material effect on your data and ensure that each number is unique.

 

[Victor Delta]

You can also add a small random number to each value -- "Your Number"
+
(Rand() /100000). As long as the denominator is large enough it should
have
no material effect on your data and ensure that each number is unique.

That's a clever idea. Thanks, I'll use it!

V

 

[Bernd P]

Hello,

RAND() does NOT guarantee non-repeating values. The likelihood is low
but greater zero...

Regards,
Bernd

 

[dhstein]

100,000 may be too large a denominator for RANK to distinguish values, so you
might try 5,000 or 10,000 depending on your data - but you can figure that
out. Bernd's comment that RAND() does not guarantee uniqueness is correct,
of course, but 1) the likelihood of a problem is small and 2) you can include
a sum of your numbers and rerun if it's wrong. For example, with 100 numbers
the total of the ranked numbers should be 1 + 2 + 3 + ... + 100 = (N) (N +
1) / 2 = 5050. So if your total is not what it should be - hit "F9" to
recalc and try again.