Summarize and sort VBA code based on deduplication greater than a certain condition

[Sakurayuki]

De-duplication of the company in column A. Summarize the detailed data with an amount >= 500 and output the list sorting VBA
Column D is de-duplicated, and the sum of the duplicates in column E must be greater than or equal to 500.

Any help is greatly appreciated

[Marc L]

According to your attachment a Windows only VBA demonstration for starters :

Sub Demo1()
    Dim V, R&
        V = [A1].CurrentRegion.Value2
            [D2].CurrentRegion.Offset(1).Clear
    With CreateObject("Scripting.Dictionary")
            For R = 2 To UBound(V):  .Item(V(R, 1)) = .Item(V(R, 1)) + V(R, 2):  Next
        For Each V In .Keys
            If .Item(V) < 500 Then .Remove V
        Next
        If .Count Then
            [D3:E3].Resize(.Count).Value2 = Application.Transpose(Array(.Keys, .Items))
            [D3:E3].Resize(.Count).Sort [D2], 1, Header:=2
           .RemoveAll
        End If
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Richard Buttrey]

Please clarify.

Do you mean the individual values in column B must be >500 to be included in the sum, or that the sum of all the individual for a company must be >500

If the former then maybe =SUMIFS($B$2:$B$27,$A$2:$A$27,D3,$B$2:$B$27,">=500")

But please clarify. Why in any case do you need VBA?

[Marc L]

According to the attachment the classic Excel basics VBA demonstration :

Sub Demo2()
     Dim A$, B$, V, W, R&
         [D2].CurrentRegion.Offset(1).Clear
    With [A1].CurrentRegion.Columns
         A = .Item(1).Address
         B = .Item(2).Address
        .Item(1).AdvancedFilter 2, , [D2], True
    End With
    With [D2].CurrentRegion
        V = .Value2
        W = Evaluate("IF({1},SUMIF(" & A & "," & .Columns(1).Address & "," & B & "))")
    For R = 2 To UBound(V)
        If W(R, 1) < 500 Then V(R, 1) = Empty Else V(R, 2) = W(R, 1)
    Next
       .Value2 = V
       .Sort .Cells(1), 1, Header:=1
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Sakurayuki]

@Marc L You are so good, Thank you for the perfect solution

[Marc L]

Thanks for the rep' !

As a reminder Demo1 should crash with more than 65K ish rows …