Simplify formula to assign unique Ranks to Col range with duplicate values and errors

  • Hi!


    I have data with duplicate values and errors in a Col. range as:


    1
    2
    3
    4
    3
    2
    #N/A
    3
    1


    In order to get a clean list with unique values and without errors (row with errors replaced with blank cells in end result), I am undertaking following steps:


    1. I use

    Code
    =IF(ISERROR(A2),"",A2)

    to remove errors from original list;


    2. I use

    Code
    =RANK(B2,$B$2:$B$10,1)

    to rank data (this assigns duplicate ranks to same values and gives errors for blank cells);


    3. I use

    Code
    =IF(ISERROR(C2),"",C2)

    to remove errors from rank list;


    4. I use

    Code
    =RANK(D2,$D$2:$D$10,1)+COUNTIF(D$2:$D2,D2)-1

    to assign unique ranks to duplicate values;


    5. I use

    Code
    =IF(ISERROR(E2),"",E2)

    to remove errors from unique rank list


    I was wondering if the above steps can be reduced/ no. of columns/ calculations reduced to get a clean list without errors with unique ranks assigned to duplicate values


    Sample file attached.


    Regards,
    Naira

  • Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er


    using only the first helper in column B, try in C2:


    =IF(B2="","",RANK(B2,$B$2:$B$10,1)+COUNTIF($B$2:B2,B2)-1)


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er


    Quote from NBVC;703043

    using only the first helper in column B, try in C2:


    =IF(B2="","",RANK(B2,$B$2:$B$10,1)+COUNTIF($B$2:B2,B2)-1)


    copied down


    Yep! works purrfect.
    Thanx a lot

  • Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er


    Hi NBVC,


    Not sure if this new issue falls in your domain expertise or not, but since it is related to the solution you gave...


    Anyways, I am attaching another sample file where your formula is not able to take care of the duplicates and shows duplicate ranks. The data in it has been copied from my actual work file so the issue is real.


    The most amazingly interesting thing is, that if I copy the same set of data to a notepad file and then copy it back to the same Col. A from which I took it, the formula works.


    Sample file with duplicates in ranks attached.

    Can you please help me out on this one too?


    - Naira

  • Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er


    It has to do with Floating-point arithmetic and how Excel stores and calculates floating point arithmetic. Usually when manipulating decimal figures, problems can occur with precision. See this Article: http://support.microsoft.com/kb/78113


    There are 2 ways to "fix this".


    1. You can check a setting in your workbook to "set precision as displayed" as explained and shown here: http://office.microsoft.com/en…recision-HA010218870.aspx


    or you can change the formula in B2 to:


    =IF(ISERROR(A2),"",ROUND(A2,3))


    copied down. Which makes the arithmetic a bit more precise.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er


    Quote from NBVC;703194


    or you can change the formula in B2 to:


    =IF(ISERROR(A2),"",ROUND(A2,3))


    copied down. Which makes the arithmetic a bit more precise.


    Setting the decimal places helps. I have been using the Round and Fixed functions quite extensively, but somehow missed the point this time.


    Thanks a lot once again for your prompt reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!