VBA & Macro Problems

Summarize Duplicate Names and Amounts with an Excel VBA Macro

Huda QurayshiHuda Qurayshi Nov 14, 2026 868 views

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.

Summarize Duplicate Names and Amounts with an Excel VBA Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, press Alt+F11, select Insert > Module, and paste the macro below into the new module.

2
Add the summary macro

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

3
Set the related-name column

In the macro, change RELATED_COL from 2 if the related names are not in column B. For example, use 3 for column C.

4
Run and check the export

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.

Use a VBA dictionary to summarize and export the data
Check related-name consistency: If the same name in column A has different related names, the macro retains only the first one. Blank names are skipped, blank amounts count as zero, and nonnumeric amounts stop the macro. The CSV export uses Excel's UTF-8 CSV format, which requires a version that supports xlCSVUTF8.
Summarize in WPS Spreadsheet

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. 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. 2. Create a PivotTable: Choose Insert > PivotTable, then place the name and related-name fields in Rows and the amount field in Values.
  3. 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. 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.
Group duplicate names and sum their amounts with a PivotTable.Open and edit Microsoft Excel XLSX and XLS files.Export a prepared summary as a CSV file.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

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.