September 13, 2004

Duped by Duplicates

I've noticed that certain functions don't return what I call "true ranking" when there are duplicates in a range.

Here's is an example. In cells B2:B21 I have 20 numbers and I want to sort them from the largest values first. To do this I have used an array formula which is =LARGE( B2:B21,ROW(INDIRECT("1:20"))). Note the braces at either end of the formula in the picture. You don't enter them yourself, they appear automatically when you push Ctrl, Shift and Enter which is how array formulas are entered (as opposed to just using Enter). Select your range first, then enter the formula as mentioned above.



So far so good. The problem is that any duplicates will cause inaccurate results. For example, I've now entered 200 into cell B7, which causes 200 to appear twice in Column C. Not good, the first and second largest values should definitely not be the same.



How to get around this? One way would be to use the wonderful Morefunc.xll add-in by Laurent Longre which contains 41 useful functions. For example, I have used the UNIQUEVALUES function here in an array to help solve the duplicates problem.



Well, this solved the problem very nicely, but unfortunately the same add-in must be installed in every computer where the file is used, so if this is the case, you might find yourself wondering if there is an alternative solution.

Here's a way that I thought up (probably not the best but it seems to work) First I'll enter a "duplicate ignoring" formula in cell C2 and drag down.

=IF(OR(B2="",COUNTIF(INDIRECT(ADDRESS(ROW($B$2),COLUMN($B$2))&":"&ADDRESS(ROW(),COLUMN()-1)),B2)>1),"",B2)

Here's how it looks.



Now I enter the same LARGE array formula as before like this.



Hmm... I don't like that #NUM! error at the bottom so I'll rewrite it like this.

=IF(ISERROR(LARGE(C2:C21,ROW(INDIRECT("1:20")))),"",LARGE(C2:C21,ROW(INDIRECT("1:20"))))

And here is the picture.



Well, that seems to do the job. Works for me!

By the way, you can read more about Mr. Longre and other Excel wizards at my friend Colo's site, Colo's Excel Junk Room, on the Cell Masters page.

G'night!

Write a Comment
Hi Andrew,

This is a great solution.
I used a similar one:
In cell C2 I enter =MAX(B2:B21)
In cell C3 I enter =IF(COUNTIF($B$2:$B$21,"<"&C2)=0,"",
LARGE($B$2:$B$21,COUNTIF($B$2:$B$21,">="&C2)+1))
and drag down C3 to cell C21.

Regards,
Zoltan from Hungary
Posted by Zoltan Till at September 16, 2004 16:02
Hi Zoltan,

That's a very good way also. I like how you use COUNTIF for both the first and second parts of the formula. I think I'll "steal" it for my tips :-)
Posted by Andrew at September 16, 2004 19:59
Hi Andrew,

I have found your page very interesting, as I am struggling with a ranking probelm. I would like to retain the duplicates, but not miss out the next highest value. For example, if I have a list of test scores, and I am ranking the position of the scores, if 3 people have the same score and are 7th in a list of 15, I want the person with the next highest score to appear in the list as having the 8th highest score, not as being ranked 10th.

How do I modify the formula to do this?

Regards,

Jim Allen
Posted by Jim Allen at February 03, 2005 01:47
Hi Jim,

This might be of some help.

http://blog.livedoor.jp/andrewe/archives/6901749.html

Andrew
Posted by Andrew at February 03, 2005 18:19