How to Consolidate Duplicate Spreadsheet Records Without Losing Data
Question details
The user needs to merge duplicate identifiers (like SSIDs) into a single row while keeping all non-blank values scattered across multiple rows and columns.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning up a disorganized dataset where unique IDs are repeated across multiple rows, and related data fields contain a mix of blanks and useful information.
- Observed behavior
- Standard duplicate removal simply deletes extra rows, resulting in the loss of valuable non-blank data associated with the duplicate identifiers.
Before merging your duplicate records, ensure your dataset has a clear unique identifier column (such as an SSID or Employee ID) and carefully decide how you want to handle columns that contain conflicting data.
Consolidate Duplicate Records Using Power Query
Power Query provides a robust, repeatable way to group duplicate records and extract non-blank values across columns without data loss.
Power Query allows you to group data by your unique identifier and apply custom aggregation rules to each remaining column. This ensures no data is arbitrarily discarded during the consolidation process.
Select your data table, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.
Select the column containing your unique IDs (e.g., SSID), right-click the column header, and choose 'Group By'.
In the Group By dialog, select 'Advanced'. Add your remaining columns and set the Operation to 'Max' (for text strings or numbers) or 'Sum' depending on how you want to merge the non-blank values.
Click 'Close & Load' in the Home tab to output the newly consolidated, duplicate-free records into a fresh worksheet.

Combine Non-Blank Values Using Formulas
Use dynamic array formulas like UNIQUE, TEXTJOIN, and FILTER to aggregate data from multiple duplicate rows into a single organized cell.
Consolidate and Clean Your Datasets with WPS Spreadsheet
WPS Spreadsheet provides powerful data handling capabilities, including dynamic array formulas and intelligent aggregation tools, making it easy to merge duplicate records without losing critical information.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your unorganized duplicate records.
- 2. Apply dynamic formulas: Use built-in functions like UNIQUE, FILTER, and TEXTJOIN to dynamically combine rows and safely preserve all non-blank values.
- 3. Leverage built-in Data tools: Alternatively, utilize the 'Consolidate' feature found under the Data tab if you simply need to calculate sums, counts, or averages for your duplicated rows.
- 4. Save your consolidated file: Save your cleanly formatted document as a standard Excel file format (.xlsx) to easily share with your colleagues.

Frequently Asked Questions
Will the standard 'Remove Duplicates' button preserve my non-blank data?
No. The standard 'Remove Duplicates' feature under the Data tab strictly keeps the first instance of a duplicate and deletes the subsequent rows entirely. This will cause you to lose any unique non-blank data present in the deleted rows. You must use data consolidation techniques or formulas instead.
How do I handle conflicting text values when merging duplicates?
If two duplicate rows have different text values in the same column, you can use the TEXTJOIN function to combine them into one cell (e.g., 'Value 1, Value 2'). If you are using Power Query, you can either extract all values or specify a rule to only keep the maximum or minimum text string.
Can I consolidate duplicate data spanning multiple different worksheets?
Yes. You can use the 'Consolidate' tool found in the Data tab to summarize and group data from separate sheets. Alternatively, you can append multiple tables together using Power Query before grouping them by their unique identifier.




