How to Extract Unique Rows from Two Excel Tables
Question details
The user needs to compare two Excel tables containing similar records and extract only the rows that are unique to each table.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing two datasets to clean up data by identifying and retaining only the records that do not overlap between the two tables.
- Observed behavior
- The user wants to remove rows that appear in both tables, isolating the data exclusive to each individual table.
Ensure both of your tables share the exact same headers and data structure. It is also helpful to place both tables in the same workbook before beginning the comparison process.
Use a Helper Column and the COUNTIF Function
Combine the cell values of each row into a single string to create a unique identifier, then use the COUNTIF function to find and filter out matching rows.
Insert a new column next to your first table. Use the ampersand symbol (&) or the CONCATENATE function to join the values of the row (e.g., =A2&B2&C2). Copy this formula down for all rows.
Repeat the previous step by adding an identical helper column to your second table, concatenating the same corresponding fields.
In a new column next to Table 1, type a COUNTIF formula to check if the combined string exists in Table 2's helper column (e.g., =COUNTIF(Sheet2!D:D, D2)). Drag the formula down to apply it to all rows.
Select the COUNTIF result column, copy it, and paste it as values to remove the formulas. Apply a filter to this column, select all rows with a value greater than 0, and delete them. The remaining rows are unique to Table 1.
Use Power Query (Get & Transform) to Identify Differences
Utilize Excel's Power Query feature to merge the two tables and automatically filter out the overlapping records.
Effortlessly Extract Unique Data with WPS Spreadsheet
WPS Spreadsheet offers powerful data management tools, including COUNTIF, text functions, and advanced filtering, making it incredibly simple to compare datasets and extract unique rows.
- 1. Open Your Tables: Launch WPS Spreadsheet and open the workbook containing your two tables.
- 2. Set Up Helper Columns: Add a new column and use the =A2&B2 formula to combine the key values for each table.
- 3. Compare Data: Use the =COUNTIF() formula to check if the data from the first table's helper column appears in the second table.
- 4. Filter Unique Rows: Go to the Data tab, apply an AutoFilter, and filter the COUNTIF column to show only '0', revealing your unique rows instantly.
Frequently Asked Questions
Can I extract unique rows based on a single column instead of the entire row?
Yes. If you have a unique identifier like an Employee ID or Invoice Number, you can skip the helper column and apply the COUNTIF function directly to that specific column to find matching or unique records.
Why is Power Query missing some duplicate rows?
Power Query comparisons are case-sensitive by default. If your data has different capitalizations (e.g., 'London' vs 'london'), Power Query will treat them as two unique rows. You can fix this by transforming the columns to uppercase or lowercase inside the Power Query Editor before merging.
Is there a way to highlight the unique rows instead of extracting them?
Yes. You can use Conditional Formatting. Combine the columns in a helper column, then apply a Conditional Formatting rule using the COUNTIF formula (e.g., =COUNTIF(Sheet2!$D:$D, $D2)=0) to highlight the rows in Table 1 that do not exist in Table 2.
