Return All Row Numbers Of Minimum Value

[DaveHappyNorm]

Hi all

I was hoping someone could help me with the following Excel problem.

I have list of numbers in A and I want to find the row number of the Min value in that column, if the minimum value occurs more than once I need all the row numbers of the minimum value.

For example:-

Row 1 15
Row 2 6
Row 3 22
Row 4 18
Row 5 6
Row 6 12

Using the data above, the minimum value is 6 and I need row numbers 2 and 5.

I found the following formula:-

=MATCH(MIN(A1:A7),A1:A7,0)

But this only works if the minimum value occurs once.

Any VBA code that would achieve this?

Thank you

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can try...

Function MinRowNums(Rng As Range) As String
  Dim R As Long, C As Long, Arr As Variant, Min As Variant
  Min = Evaluate("MIN(" & Rng.Address & ")")
  Arr = Rng.Value
  For R = 1 To UBound(Arr, 1)
    For C = 1 To UBound(Arr, 2)
      If Arr(R, C) = Min Then MinRowNums = MinRowNums & " " & R
    Next
  Next
  MinRowNums = Replace(Trim(MinRowNums), " ", ", ")
End Function

The argument for this function is the range you want to check, so for your posted example, you put this into a cell...

=MinRowNums(A1:A6)