VBA to Summarise Amount based on Unique ID column

[dids86]

Hi,

I'd like to be able to utilise a VBA whereby the data is summarised by amount based on a unique id column as per the below.

Thank you for your help

Tom

 

[mumps]

Start by adding a new sheet named "Summary" and add the headers in row 1 to match the headers in your data sheet. Then with your data sheet being the active sheet, run this macro:

Sub SummarizeData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, dic As Object, desWS As Worksheet, tot As Long
    Set desWS = Sheets("Summary")
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 6)) Then
            dic.Add v(i, 6), Nothing
            Range("A1").AutoFilter 6, v(i, 6)
            tot = WorksheetFunction.Sum(Range("G2", Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlVisible))
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 7).Value = Array(v(i, 1), v(i, 2), v(i, 3), v(i, 4), v(i, 5), v(i, 6), tot)
        End If
    Next i
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub