Summarize Duplicate Names and Amounts with an Excel VBA Macro
Question details
Combine duplicate names and their amounts into a summary with related names, totals, and a grand total, then export the result as a CSV file.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Data starts in row 8, with names in column A and amounts in column E. The summary should begin in column J and support an automated CSV export.
- Observed behavior
- The desired result has one row per unique name, a related name in column K, a total in column L, and a grand total below the summary.
Save the Excel workbook as an .xlsm file and identify the source column containing the related names; the macro below assumes column B. Make sure columns J through L from row 8 downward are available, because the macro clears that area before writing the summary.
Use a VBA dictionary to summarize and export the data
Run a macro to group names from column A, sum amounts from column E, write the results to columns J through L, and create a CSV file.
This example uses a late-bound Scripting.Dictionary, which is typically available in Excel for Windows. It treats names as case-insensitive and keeps the first related name found for each name.
The macro exports the summary, including the grand total, to summary.csv in the saved workbook's folder. It does not export the source data or add column headers.
In Excel, press Alt+F11, select Insert > Module, and paste the macro below into the new module.
Sub SummarizeAndExport() Const RELATED_COL As Long = 2 ' Column B; change if needed Dim ws As Worksheet, exportBook As Workbook Dim totals As Object, related As Object Dim lastRow As Long, r As Long, outRow As Long Dim key As Variant, nameText As String Dim amount As Double, grandTotal As Double Dim csvPath As String If ThisWorkbook.Path = "" Then MsgBox "Save this macro-enabled workbook first." Exit Sub End If Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 8 Then MsgBox "No names found from row 8 onward." Exit Sub End If Set totals = CreateObject("Scripting.Dictionary") Set related = CreateObject("Scripting.Dictionary") totals.CompareMode = vbTextCompare related.CompareMode = vbTextCompare For r = 8 To lastRow If IsError(ws.Cells(r, "A").Value) Then MsgBox "Invalid name in row " & r Exit Sub End If nameText = Trim(CStr(ws.Cells(r, "A").Value)) If nameText <> "" Then If IsError(ws.Cells(r, "E").Value) Then MsgBox "Invalid amount in row " & r Exit Sub End If If IsEmpty(ws.Cells(r, "E").Value) Or ws.Cells(r, "E").Value = "" Then amount = 0 ElseIf IsNumeric(ws.Cells(r, "E").Value) Then amount = CDbl(ws.Cells(r, "E").Value) Else MsgBox "Invalid amount in row " & r Exit Sub End If If totals.Exists(nameText) Then totals(nameText) = totals(nameText) + amount Else totals.Add nameText, amount related.Add nameText, ws.Cells(r, RELATED_COL).Value End If End If Next r If totals.Count = 0 Then MsgBox "No nonblank names found." Exit Sub End If ws.Range("J8:L" & ws.Rows.Count).ClearContents outRow = 8 For Each key In totals.Keys ws.Cells(outRow, "J").Value = key ws.Cells(outRow, "K").Value = related(key) ws.Cells(outRow, "L").Value = totals(key) grandTotal = grandTotal + totals(key) outRow = outRow + 1 Next key ws.Cells(outRow, "J").Value = "Grand Total" ws.Cells(outRow, "L").Value = grandTotal Set exportBook = Workbooks.Add(xlWBATWorksheet) exportBook.Worksheets(1).Range("A1").Resize(outRow - 7, 3).Value = _ ws.Range("J8:L" & outRow).Value csvPath = ThisWorkbook.Path & Application.PathSeparator & "summary.csv" exportBook.SaveAs Filename:=csvPath, FileFormat:=xlCSVUTF8 exportBook.Close SaveChanges:=False MsgBox "Summary exported to " & csvPath End Sub
In the macro, change RELATED_COL from 2 if the related names are not in column B. For example, use 3 for column C.
Return to the worksheet containing the data, press Alt+F8, select SummarizeAndExport, and click Run. Check columns J through L and open summary.csv from the workbook's folder; respond to any prompt if that file already exists.

Create a PivotTable summary without VBA
Use this option for an interactive summary when automatic CSV creation is not required.
Summarize duplicate names and export a CSV with WPS Office
Use a PivotTable in WPS Spreadsheet to group names, sum amounts, and prepare a CSV for upload. This is a manual alternative to the automated Excel VBA macro.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and select the data range, including headers for the name, related-name, and amount columns.
- 2. Create a PivotTable: Choose Insert > PivotTable, then place the name and related-name fields in Rows and the amount field in Values.
- 3. Check the summary: Set the amount field to Sum and check the PivotTable layout and grand total. If a name has multiple related names, review the separate grouped rows.
- 4. Save the CSV: Copy the summary values to a separate sheet or workbook, then choose File > Save As and select CSV as the file type.

Frequently Asked Questions
How do I change the column used for related names?
In the VBA editor, change RELATED_COL at the top of the macro. Use the column number: 2 for B, 3 for C, and so on.
What happens if duplicate names have different related names?
The macro keeps the related name from the first matching row. If each name and related-name combination needs its own total, the grouping logic must be changed.
Where does the macro save the CSV file?
It saves summary.csv in the same folder as the workbook containing the macro. Save that workbook first, and check any overwrite prompt if summary.csv already exists.
Why does the macro stop on an amount?
The macro stops if a nonblank amount in column E is not numeric or contains an Excel error. Correct the indicated row and run it again; blank amounts are treated as zero.
Can a PivotTable replace the automated VBA export?
A PivotTable can produce the summary, but the steps shown require you to copy and save the results as CSV manually. Use the VBA macro if you need the summary and export to run together.




