Data Import & Export

How to Consolidate Duplicate Spreadsheet Records Without Losing Data

Natalie TaylorNatalie Taylor Nov 10, 2026 869 views

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.

How to Consolidate Duplicate Spreadsheet Records Without Losing Data
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 you start

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.

Solution 1Recommended

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.

1
Load your data into Power Query

Select your data table, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.

2
Group by the unique identifier

Select the column containing your unique IDs (e.g., SSID), right-click the column header, and choose 'Group By'.

3
Define your aggregation rules

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.

4
Load the consolidated dataset

Click 'Close & Load' in the Home tab to output the newly consolidated, duplicate-free records into a fresh worksheet.

Consolidate Duplicate Records Using Power Query
Automated Updates: Once configured, you can refresh this Power Query connection anytime your source data updates, automatically consolidating new duplicate records.
Organize Data Effectively

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. 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your unorganized duplicate records.
  2. 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. 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. 4. Save your consolidated file: Save your cleanly formatted document as a standard Excel file format (.xlsx) to easily share with your colleagues.
Fully compatible with Microsoft Excel (.xlsx) file formats and dynamic formulas.Lightweight software optimized for lightning-fast processing of large datasets.Free to download and use with a highly familiar user interface.Built-in data validation and consolidation tools to streamline your daily workflow.
QA img-9

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.