# Rank a range of data containing duplicates SEQUENTIALLY

1. ## Rank a range of data containing duplicates SEQUENTIALLY

As the title indicates, I would like to rank data in a given range. However, the "Rank.EQ" function does not rank number sequentially if duplicates are present. The result ends up being 1,2,3,4,4,4,7,8,9,10... instead of 1,2,3,4,5,6,7,8,9,10... I have tried several work arounds, but I can't seem to wrap my head around how to get them to work properly.

here are some examples of various codes I have tried...

Example 1:

=SUM(1*(A3<\$A\$3:\$E\$7))+1+IF(ROW(A3)-ROW(\$A\$3)=0,0,SUM(1*(A3=OFFSET(\$A\$3,0,0,INDEX(ROW(A3)-ROW(\$A\$3)+1,1)-1,1))))

Example 2:
=RANK(A3,\$A\$3:\$E\$7,0)+COUNTIF(\$A\$3:A3,A3)-1

The code in the first example does not work properly when negatives are present in the range of data.

The code in the second example does not work properly when the range of data is extended to multiple columns.

I have attached the excel spreadsheet for your viewing. As you will see, My end goal is to conditionally format the TOP FIVE results (either top FIVE most negative or top FIVE most positive). The highlighted cells must also be greater than 500 or -500 depending on what report i run on the given data range. This concept will be applied to larger spreadsheets to highlight the top most "material" deviance's.

This was a simple task using the AND function and RANK function, that is until i realized i could potentially run into issues with duplicates! I really could use some help on this!

2. ## Re: Rank a range of data containing duplicates SEQUENTIALLY

Hi and welcome to the forum

You have so many different attempts in that file Im not sure what your expected outcome is. If you want to exclude duplicates in the ranking I would use a helper with a =if(countif(\$a\$1:A1,A1)>1,"",A1) copied down, and then base the ranking on that helper

3. ## Re: Rank a range of data containing duplicates SEQUENTIALLY

Pl see the attached file with UDF.

4. ## Re: Rank a range of data containing duplicates SEQUENTIALLY

find the attachment

5. ## Re: Rank a range of data containing duplicates SEQUENTIALLY

Thank you for both reply's! Creating a UDF seems like the cleanest solution. Will a UDF be backwards compatible with older versions of excel?

6. ## Re: Rank a range of data containing duplicates SEQUENTIALLY

yes, it works with excel 2003.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1