Grouping similar items in excel

have Some Values in excel
and I wish to collect them as this pic. how can i do this in vba untitled.JPG

=sumif

if aaa is at a1 and value at b
beside aaa cell put =sumif(a1,a:a,b:b)

I Need this to be done using vba (the below table to be created with the values from the upper table)

can specific the column and row for the name and value? thanks..

Column A for name
Column B for Value

Sub TEST()

Dim LastRow As Long

LastRow = Worksheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow
    
    If IsEmpty(Worksheets("sheet1").Range("A" & i)) = False Then
        
        Worksheets("sheet1").Cells(i, 4) = Worksheets("sheet1").Range("A" & i)
        Worksheets("sheet1").Cells(i, 5) = Application.SumIf(Range("A:A"), Range("A" & i), Range("B:B"))
    
    End If
    

    
Next i

For i = 1 To LastRow

    If Worksheets("sheet1").Cells(i + 1, 4) = Worksheets("sheet1").Cells(i, 4) Then
    
        Worksheets("sheet1").Cells(i, 4).Clear
        Worksheets("sheet1").Cells(i, 5).Clear
        
    End If

Next i

End Sub