 # 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

## Files

• 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

## Files

• 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!