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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
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
 
Upvote 0
Hi Joe:

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

=MAX(IF(range<>MAX(range),range))
 
Upvote 0
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?
 
Upvote 0
Joe,

if your PS was still a question, then I'm missing the sense
(a bit ill so could be me)
best regards,
Erik
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top