How to Combine Duplicate Customer Rows in Excel Without Ablebits
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.

- 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 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.
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.
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.
Select the column containing the Customer Names. Navigate to the 'Home' tab in the Power Query Editor and click 'Group By'.
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).
Click 'Close & Load' in the top-left corner to output the cleaned, consolidated customer list into a new worksheet.

Analyze and Consolidate Using PivotTables
If your goal is data analysis rather than permanently altering the dataset, PivotTables provide a dynamic way to summarize duplicates.
Combine Text from Duplicate Rows Using UNIQUE and TEXTJOIN
For modern versions of Excel (Office 365 or Excel 2019+), you can use dynamic array formulas to extract unique customers and merge their purchases into a single cell.
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. Open your data file: Launch WPS Spreadsheet and open your existing Excel workbook containing the duplicate customer data.
- 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. Use advanced formulas: Utilize built-in functions like =UNIQUE() and =TEXTJOIN() to create custom summary tables for your customer records.
- 4. Save seamlessly: Save your consolidated workbook in the standard .xlsx format to ensure compatibility with all users.

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.




