VBA & Macro Problems

How to Consolidate Excel Rows by Matching Columns A and E

Ayan Masood Sep 27, 2026 869 views

Question details

The user needs to combine rows in a spreadsheet where the values in both Column A and Column E are identical, while summing the corresponding numerical values in Column C. Rows with different values in Column E should remain separate.

Consolidate Excel Rows by Matching Columns A and E Using VBA

Product: Excel
Device & OS: not provided
Scenario: Summarizing and organizing duplicate records in a large dataset by matching multiple criteria columns simultaneously.
Observed behavior: Duplicate entries across two specific columns need to be merged into a single consolidated row, with a third column providing the sum of their values, while keeping non-matching rows distinct.

Before you start

Before running any VBA macros, ensure you have saved a backup copy of your workbook, as macro actions cannot be undone using the standard Undo feature.

Solution 1Recommended

Use a VBA Dictionary to Group and Sum Rows

Using a VBA Scripting Dictionary is the most efficient programmatic way to group data by a composite key (combining Columns A and E) and sum the corresponding values.

This method creates a unique identifier by combining the text from Column A and Column E. It stores this key in a dictionary, checks subsequent rows for the same key, and dynamically adds up the values in Column C before outputting the cleaned data.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications window in your spreadsheet software.

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to open a blank script window.

Create the Dictionary and Composite Key

Write a macro that initializes a Object (e.g., CreateObject("Scripting.Dictionary")). Loop through your data rows and define a composite key by concatenating the values, such as Key = Cells(i, 1).Value & "|" & Cells(i, 5).Value.

Sum the Values in Column C

Use an If statement to check if the dictionary already contains the Key. If it exists, add the current row's Column C value to the existing total. If it does not exist, add the new Key and its Column C value to the dictionary.

Output the Consolidated Data

Write a final loop to extract the keys and their summed values from the dictionary, and print them onto a new worksheet or specific output range.

Use a VBA Dictionary to Group and Sum Rows

Include a delimiter in your key: Always use a delimiter like a pipe (|) when concatenating keys (e.g., A & "|" & E) to prevent false matches from merged text strings.

Solution 2

Use a Pivot Table (No-Code Alternative)

If you prefer not to write VBA code, you can achieve the exact same consolidated result using a built-in Pivot Table.

Consolidate Data Faster with WPS Spreadsheet

WPS Spreadsheet fully supports advanced VBA macros and provides highly intuitive built-in tools like Pivot Tables to help you consolidate and summarize complex data without hassle.

1. Open Your Dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your duplicate rows.
2. Access the Developer Tools: Navigate to the 'Developer' tab and click 'VBA Editor' to paste and run your dictionary consolidation macro.
3. Use Built-in Consolidation: Alternatively, go to the 'Data' tab and explore the 'Consolidate' or 'PivotTable' features for a visual approach.
4. Save as Macro-Enabled: Save your finalized document directly as a .xlsm file to keep your VBA scripts intact for future use.

Fully compatible with Microsoft Excel file formats (.xlsx and .xlsm).Built-in VBA editor allows you to run existing macros seamlessly.Powerful Pivot Tables for quick, no-code data summarization.Free, lightweight, and easy-to-use alternative to heavy office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my rows not consolidating in VBA even if they look identical?

This usually happens due to hidden leading or trailing spaces in your cells. Use the Trim() function in your VBA code when creating the dictionary key to ensure exact text matches.

Can I consolidate rows based on three or more columns?

Yes. You can expand the composite key in your VBA macro to include additional columns by concatenating them, such as Key = ColA & "|" & ColE & "|" & ColF.

How do I run the VBA macro once I have added the code?

Press Alt + F8 to open the Macro dialog box, select your newly created consolidation macro from the list, and click 'Run'.

Is it possible to retain the first instance of other non-matched columns when consolidating?

Yes, but you will need to expand the VBA dictionary to store an array or a custom object, allowing you to hold values from other columns alongside the summed total for Column C.

Excel & Data Problems