Remove Duplicates

no problems it takes a while to get the hang of it
this one is for extraction the duplicates and removes the spaces

Option Explicit
Sub ptestu()
    Dim p!, i!, k(), e
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
        ReDim k(UBound(e, 1), 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = vbTextCompare
            For i = 1 To UBound(e, 1)
                If Not IsEmpty(e(i, 1)) Then
                    e(i, 1) = Trim(e(i, 1))
                    If Not .exists(e(i, 1)) Then
                        k(p, 0) = e(i, 1)
                        .Add e(i, 1), p
                        p = p + 1
                    End If
                End If
            Next
        End With
        .ClearContents
    End With
    Range("a1").Resize(p, 1).Value = k
End Sub

this one is to clean the duplicated raw data
it was for your calculation as they woudnt sum while the spaces

Sub ptwo()
Dim p!, i!, k(), e
With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
    p = .Rows.Count
 
 ReDim k(UBound(e, 1), 1)
For i = 1 To UBound(e, 1)
  k(i, 1) = Trim(e(i, 1))
 Cells(i, 1) = k(i, 1)
Next
End With
End Sub