VBA - Find 2nd minimum in list with duplicates

[JeanRene]

Hello,

I'd like to have the vba formula to find the row of the 2nd smallest number in a liste where number, e.g. if I have a list with 9;10;9;10;11; the formula would give me the row number of the first 10 it encounters.

I found an array formula using "large" and "if" to find the 2nd smallest if but it doesn't seem to work if there is only 2 values in the list (if I have 9;10;9;10 it shows 10 as result) and I don't know how to write it in vba...

Any help is welcome!

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Values​

[/td][td]

2nd Largest​

[/td][td]

Row​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

9​

[/td][td]

9​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

9​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

10​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in B2
=LARGE(A2:A5,COUNTIF(A2:A5,LARGE(A2:A5,1))+1)

Formula in C2
=MATCH(B2,A:A,0)

M.

 

[Peter_SSs]

For the same layout as Marcelo has shown, another option for cell B2 would be
=AGGREGATE(14,6,A2:A5/(A2:A5<MAX(A2:A5)),1)

 

[Peter_SSs]

I missed that you wanted vba. If that is still the case, try

Sub SecondSmallest()
  Dim Small2 As Double
  Dim rw As Long
  Dim adr As String
  
  adr = "A2:A" & Range("A" & Rows.Count).End(xlUp).Row
  Small2 = Evaluate(Replace("aggregate(14,6,#/(#<max(#)),1)", "#", adr))
  rw = Evaluate("match(" & Small2 & "," & Range(adr).EntireColumn.Address & ",0)")
  MsgBox "2nd smallest: " & Small2 & vbLf & "Row: " & rw
End Sub

 

[JeanRene]

Thank you Peter, indeed I need in VBA

That seems to work for me, however if I have a number in the list with decimal (for example 100;100;100,001;101;101;102) it doesn't seems to work?

Also, for my understanding, could you explain how the hashtag in the "Small2 = Evaluate(Replace("aggregate(14,6,#/(#<max(#)),1)", "#", adr))" part works?

Thank you

 

[JeanRene]

Also, if I have a list with only 2 values like 100;100;100;101;100 it will return 100, while it should be 101. It is a special case, but I might encounter it...

 

[Peter_SSs]

.. if I have a number in the list with decimal (for example 100;100;100,001;101;101;102) it doesn't seems to work?

Also, if I have a list with only 2 values like 100;100;100;101;100 it will return 100, while it should be 101.

I think both issues are because I followed Marcelo's lead and misread you question and was providing you with the 2nd largest value, not second smallest.

So, try this instead

Sub SecondSmallest_v2()
  Dim Small2 As Double
  Dim rw As Long
  Dim adr As String
  
  adr = "A2:A" & Range("A" & Rows.Count).End(xlUp).Row
  Small2 = Evaluate(Replace("aggregate(15,6,#/(#>min(#)),1)", "#", adr))
  rw = Evaluate("match(" & Small2 & "," & Range(adr).EntireColumn.Address & ",0)")
  MsgBox "2nd smallest: " & Small2 & vbLf & "Row: " & rw
End Sub

Also, for my understanding, could you explain how the hashtag in the "Small2 = Evaluate(Replace("aggregate(14,6,#/(#<max(#)),1)", "#", adr))" part works?

Applying your question to this new version, where this line is slightly different

Small2 = Evaluate(Replace("aggregate(15,6,#/(#>min(#)),1)", "#", adr))

The # is just a place-holder for the address of the range we are dealing with. This syntax allows me to just have a single string in the Evaluate() section and replace all the instances of # with the address. It saves having to concatenate several strings and variable names. The alternative, which in this case isn't much longer but fiddlier to write & get all the quote marks correct, would be

Small2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")

 

[Marcelo Branco]

I think both issues are because I followed Marcelo's lead and misread you question and was providing you with the 2nd largest value, not second smallest.

Peter and Jean

I answered too quickly. Sorry for causing this mess

M.

 

[Peter_SSs]

Peter and Jean

I answered too quickly. Sorry for causing this mess

M.

Not your fault Marcelo, I only had to read the post carefully myself.

 

[Marcelo Branco]

Not your fault Marcelo, I only had to read the post carefully myself.

I should have read the question more carefully either.

M.