2nd highest unique value in unsorted list with duplicates

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Have random data, that cannot be sorted and the list has duplicates. Need to find the Nth highest unique value [and lowest], Given:

0.4, 0.25, 0.45, 0.45, 0.2, 0.6, 0.4, 0.6

I should get: 0.45
Not 0.6 [as their is two 0.6 values in the list] all the formulas I have tried give the Max or the Nth Max but not the Nth unique Max.

Anyone know the trick to this Sheet Formula?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Joe,
would this be an idea ?
(data is your range with numbers)
Code:
=LARGE(data,1+COUNTIF(data,LARGE(data,1)))
kind regards,
Erik
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good evening JoeWas

I think that this routine should be up to the job. The only problem I see is that it doesn't work if you are in column A - a limitation of my "unique values" routine I'm afraid, but it seems to work. Also, for anyone else looking at it, it's a very quick and dirty routine - I've been a very naughty boy and not declared variables etc but it's late and I'm going to bed now.

Sub Test()
Set UsrRnge = Selection
Dup = 0
On Error Resume Next
For Each UsrCell In UsrRnge
DupFind = UsrCell.Address
DupFind = UsrRnge.Find(What:=UsrCell, After:=UsrCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False).Address
If UsrCell.Value <> "" And DupFind = UsrCell.Address Then
Dup = Dup + 1
If Dup = 1 Then Set Duplicate = UsrCell
If Dup <> 1 Then Set Duplicate = Union(Duplicate, UsrCell)
End If
Next UsrCell
Duplicate.Select
MsgBox Application.WorksheetFunction.Large(UsrRnge, 3)
End Sub

HTH

DominicB
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Joe:

For the second highest value with repeats in the range ... how about the following array formula ...

=MAX(IF(range<>MAX(range),range))
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Thanks, I will test when I get back to the office.

Erik, looks like what I need, hope it works for whatever Nth I am looking for?
It looks like something I tried which seemed to work, but was not consistant for all positions?
May be yours will work better.

Dom, thanks, and I thought I would never say this, but in this case I cannot use VBA. I will check it out anyway.

Yogi, Thanks: Chaining this as nested "IF's would get me seven levels, was wishing for a way to ID the Nth level in the formula directly so it could be controlled by a cell value?

PaddyD, will look into this more.

Will let you all know if I find the perfect fit, so far Erik's looks most like what I need.

P.S. Along Erik's line and using his notation, the one that I tried is this:

=IF(D1=1,MAX(dataR),LARGE(dataR,ABS((D1-1))+COUNTIF(dataR,LARGE(dataR,(ABS(D1-1))))))

it's problem is if the control cell "D1" is an Nth position greater than actual Nth position it will go through the list twice before erroring out, once forward and second backwards. That is "D1" can be up to 2 times the available "N" positions?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Joe,

if your PS was still a question, then I'm missing the sense
(a bit ill so could be me)
best regards,
Erik
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,941
Maybe...

=LARGE(IF(A1:A10<>"",IF(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)-ROW(A1)+1,A1:A10)),2)

The formula will return 0.45 for the second highest, and 0.4 for the third highest, and so on...

Hope this helps!
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Thanks Dom, that array formula corrected the problems with what I was using.

Thanks Erik and the rest of you, all taken care of now.
 

Forum statistics

Threads
1,078,474
Messages
5,340,550
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top