VBA: SUMIF then left with largest values

[jcooooper]

Hi all,

The below code populates a unique list, which is about 2000 rows of text.

I need to then to SUMIF the results, using named ranges I have setup:

Range="Security_description"
Criteria = the text generated in the unique list above
Sum range = "notional_value"

Once the SUMIF is complete, I need to be left with the largest 40 values and delete the remainder.

Any ideas? I'm unsure how to translate the formulas into VBA.

Many thanks for any help!

Sub Get_unique_security()




Application.ScreenUpdating = False




Dim v
v = getUniqueArray(Range("Aladdin_security"))
If IsArray(v) Then
   Range("A3").Resize(UBound(v)) = v


End If


Range("Aladdin_security_description").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "Cash"


Application.ScreenUpdating = True


End Sub








Public Function getUniqueArray(inputRange As Range, _
                                Optional skipBlanks As Boolean = True, _
                                Optional matchCase As Boolean = True, _
                                Optional prepPrint As Boolean = True _
                                ) As Variant
               
Dim vDic As Object
Dim tArea As Range
Dim tArr As Variant, tVal As Variant, tmp As Variant
Dim noBlanks As Boolean
Dim cnt As Long
                      
On Error GoTo exitFunc:
If inputRange Is Nothing Then GoTo exitFunc


With inputRange
    If .Cells.Count < 2 Then
        ReDim tArr(1 To 1, 1 To 1)
        tArr(1, 1) = .Value2
        getUniqueArray = tArr
        GoTo exitFunc
    End If


    Set vDic = CreateObject("scripting.dictionary")
    If Not matchCase Then vDic.compareMode = vbTextCompare
    
    noBlanks = True
    
    For Each tArea In .Areas
        tArr = tArea.Value2
        For Each tVal In tArr
            If tVal <> vbNullString Then
                vDic.Item(tVal) = Empty
            ElseIf noBlanks Then
                noBlanks = False
            End If
        Next
    Next
End With


If Not skipBlanks Then If Not noBlanks Then vDic.Item(vbNullString) = Empty


'this is done just in the case of large data sets where the limits of
'transpose may be encountered
If prepPrint Then
    ReDim tmp(1 To vDic.Count, 1 To 1)
    For Each tVal In vDic.Keys
        cnt = cnt + 1
        tmp(cnt, 1) = tVal
    Next
    getUniqueArray = tmp
Else
    getUniqueArray = vDic.Keys
End If


exitFunc:
Set vDic = Nothing
End Function