Previous Topic Next Topic

Getting multiple largest values with specified limits

Closed
M - Updated on Jun 20, 2019 at 11:53 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jun 18, 2019 at 11:57 AM
Hello, a1 is 110,a2 is 236,a3 is 175,a4 is 352,a5 is 257,a6 is 175.In b1 find value is greater than or equal to 400.i need all answers numbers not repeated but descending order all values in d1itself.That is D1 answer is 352.
In C2 find value 200, in d2 answer 257,236. In c3 find value 100,in d3 answer is 175,110. (175 values 2 times in data,but I need value shows only one time and descending order).

1 reply

Answer 1 / 1
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 18, 2019 at 11:57 AM
Hi M,

Excel doesn't have a built in function for that, so let's use a user defined one: 
Function GetResult(myRange As Range, vLow, vHigh As Long)
Dim x As Integer, mValue, cDupes As Long

mrows = myRange.Rows.Count
For x = 1 To mrows
    mValue = Application.WorksheetFunction.Large(myRange, x)
    If mValue >= vLow And mValue < vHigh And mValue <> cDupes Then
        cDupes = mValue
        GetResult = GetResult & mValue & ","
    End If
Next x

GetResult = Left(GetResult, Len(GetResult) - 1)
End Function


Paste the code in a standard module, then in Excel use it like this:
=GetResult(myRange, vLow)
=GetResult(your range, from the lowest value)
Formula for D2:
=GetResult($A$1:$A$6,C2)

Best regards,
Trowa
0