2nd highest unique value in unsorted list with duplicates

[Joe Was]

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]

Hi, Joe,
would this be an idea ?
(data is your range with numbers)

=LARGE(data,1+COUNTIF(data,LARGE(data,1)))

kind regards,
Erik

 

[Domenic]

*Deleted*

 

[DominicB]

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]

Hi Joe:

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

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

 

[PaddyD]

& also see the general construction aladin put together re 'top n' indexing problems, eg:

http://www.mrexcel.com/board2/viewtopic.php?t=69970

 

[Joe Was]

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]

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]

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]

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.