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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Victor Delta" wrote:

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

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
u4sual, 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

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


On Apr 6, 6:50*pm, "Victor Delta" wrote:
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

Hello,

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

Regards,
Bernd

"Bernd P" wrote in message
...
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

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" wrote:

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