Power Query Problems

How to Combine Duplicate Customer Rows in Excel Without Ablebits

Phi Hung VoPhi Hung Vo Oct 22, 2026 871 views

Question details

The user needs to manage and consolidate duplicate customer records in Excel without relying on third-party add-ins like Ablebits, while retaining essential transaction details like purchases, dates, and services.

How to Combine Duplicate Customer Rows in Excel Without Ablebits
Product
Microsoft Excel
Device & OS
not provided
Scenario
Organizing a customer database where individuals appear multiple times due to multiple purchases, requiring consolidation without losing historical transaction records.
Observed behavior
Customer records are duplicated across multiple rows for each transaction, making data analysis difficult and requiring a native Excel method to merge or summarize the data.
Before you start

Before consolidating your data, it is highly recommended to format your raw dataset as an Excel Table (Ctrl+T) and create a backup copy to ensure you do not accidentally lose any granular transaction details.

Solution 1Recommended

Use Power Query to Clean and Combine Customer Data

Power Query is the most robust native Excel tool for grouping duplicate rows and summarizing data while preserving individual transactions.

By utilizing the 'Group By' feature in Power Query, you can consolidate duplicate customer names into single rows. You can choose to sum up transaction amounts or use the 'All Rows' operation to keep the detailed purchase history nested within the consolidated record.

1
Load data into Power Query

Select any cell inside your data table, go to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

2
Group the data

Select the column containing the Customer Names. Navigate to the 'Home' tab in the Power Query Editor and click 'Group By'.

3
Configure grouping settings

In the Group By dialog box, select 'Advanced'. Group by your customer name, and add new aggregated columns (e.g., choose 'Sum' for total amount, or 'All Rows' to keep transaction details).

4
Load the combined data

Click 'Close & Load' in the top-left corner to output the cleaned, consolidated customer list into a new worksheet.

Use Power Query to Clean and Combine Customer Data
Power Query Advantage: Using Power Query creates a repeatable process. When new customer data is added to your original table, you simply right-click the output and select 'Refresh'.
Efficient Data Management with WPS Office

Easily Combine and Analyze Data in WPS Spreadsheet

WPS Spreadsheet provides powerful data consolidation tools, advanced array functions (like UNIQUE and FILTER), and intuitive PivotTables to help you manage duplicate records effortlessly.

  1. 1. Open your data file: Launch WPS Spreadsheet and open your existing Excel workbook containing the duplicate customer data.
  2. 2. Insert a PivotTable: Go to the Insert tab and click PivotTable to dynamically group and sum duplicate customer rows without deleting any details.
  3. 3. Use advanced formulas: Utilize built-in functions like =UNIQUE() and =TEXTJOIN() to create custom summary tables for your customer records.
  4. 4. Save seamlessly: Save your consolidated workbook in the standard .xlsx format to ensure compatibility with all users.
Fully compatible with Microsoft Excel (.xlsx) formatsSupports modern functions like UNIQUE, FILTER, and TEXTJOINIntuitive PivotTable creation for rapid data summarizationCompletely free and lightweight alternative to heavy data tools
microsoft office alternative - wps office

Frequently Asked Questions

Can I combine duplicate rows in Excel without losing other data columns?

Yes. By using Power Query's 'Group By' feature and selecting 'All Rows' as your aggregation type, or by using a PivotTable, you can group identical customer names together while preserving their individual transaction dates and amounts.

How do I merge text values from duplicate rows into one cell?

You can combine the TEXTJOIN and FILTER functions. For example, =TEXTJOIN(", ", TRUE, FILTER(Product_Range, Customer_Range="Customer Name")) will combine all products bought by a specific customer into a single, comma-separated cell.

Why is it important to add a unique ID to my data before combining rows?

A unique identifier (like an Index column or Transaction ID) ensures that two separate purchases made by the same customer on the same day aren't accidentally recognized as a complete duplicate row and incorrectly deleted.