# 2nd highest unique value in unsorted list with duplicates

#### Joe Was

##### MrExcel MVP
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

*Deleted*

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 = 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

Hi Joe:

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

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

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?

Joe,

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

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!

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.

Replies
1
Views
157
Replies
6
Views
226
Replies
1
Views
101
Replies
9
Views
364
Replies
4
Views
276

1,196,155
Messages
6,013,763
Members
441,781
Latest member
Gian Carlos

### 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.

### Which adblocker are you using?

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

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