VBA to summarize data

Question

Hi, I have data in a sheet from row A8 to column F from 8 to ?? (last row), in column A I have names and in column E I have the amounts, names in column A can be duplicated due to having different amounts, what I need to do in the same sheet starting in column J, is a summary which will contain unique values from column A, names from Column B and the total from column E as an example

Entity: Employee Company number	Employee Internal ID (DNU): Full Name (Furigana)	Transaction Type: Long Name	Transaction Number	Total Expenses in Local Currency	Entity: Local Currency
100	AA	Expense Report	5283	100	US Dollar
101	BB	Expense Report	5287	150	US Dollar
100	AA	Expense Report	5291	100	US Dollar

                                                 Total  350

The summary will Show

100     AA   200

101     BB   150

Total           350

Thank you

Answer 1

Try this modified version:

Sub Summary()

    Dim Cl As Range

    Dim D1 As Object

    Dim D2 As Object

    Set D1 = CreateObject("Scripting.Dictionary")

    Set D2 = CreateObject("Scripting.Dictionary")

    For Each Cl In Range("A8", Range("A" & Rows.Count).End(xlUp))

        D1.Item(Cl.Value) = D1.Item(Cl.Value) + Cl.Offset(, 4).Value

        D2.Item(Cl.Offset(, 1).Value) = D2.Item(Cl.Offset(, 1).Value) + 1

    Next Cl

    Range("J8").Resize(D1.Count).Value = Application.Transpose(D1.Keys)

    Range("K8").Resize(D2.Count).Value = Application.Transpose(D2.Keys)

    Range("L8").Resize(D1.Count).Value = Application.Transpose(D1.Items)

End Sub

Answer 2

Thank you so much HansV, it worked

Regards, Eduardo

Answer 3

Thank you HansV for the answer, I am looking for a VBA solution since it has to be uploaded as a CSV file, and the process needs to be automatic, thank you

I so far find this part of code but don't know how to modify it to pick up the three columns needed

Sub Summary()

   Dim Cl As Range

   With CreateObject("scripting.dictionary")

      For Each Cl In Range("A8", Range("A" & Rows.Count).End(xlUp))

         .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 1).Value

       Next Cl

      Range("J8").Resize(.Count).Value = Application.Transpose(.Keys)

      Range("K8").Resize(.Count).Value = Application.Transpose(.items)

   End With

End Sub

Thank you for your help, Eduardo

Answer 4

You can create a pivot table for this purpose. Add Entity: Employee Company number and Employee Internal ID (DNU): Full Name (Furigana) to the Rows area and Total Expenses in Local Currency to the Values area. Display the pivot table in Tabular form, and specify No Subtotals.