Function Problems

Find and Remove Duplicate Customer Records in Two Excel Lists

Nimra MalikNimra Malik Nov 18, 2026 870 views

Question details

Combine two customer-number lists, copy repeated records elsewhere, and remove them from the combined data.

Find and Remove Duplicate Customer Records in Two Excel Lists
Product
Microsoft Excel
Device & OS
not provided
Scenario
Two customer lists need to be combined and checked for repeated customer numbers.
Observed behavior
The desired result is a combined list containing only customer numbers that occur once, with repeated records copied to another sheet or workbook.
Before you start

Make sure both lists use the same columns and that customer numbers use a consistent format, including any leading zeros. Place the combined data on a sheet without unrelated data in the rows you may delete.

Solution 1Recommended

Use COUNTIF and a filter to copy and remove repeated records

Use this method to copy every row with a repeated customer number, then remove all occurrences of those numbers from the combined list.

This method counts repetitions anywhere in the combined list, including repeats within one original list. It does not distinguish those from customer numbers found in both lists.

1
Combine the lists

Copy the second list and paste its data rows below the first list, aligning the customer-number and other record columns. Keep one header row.

2
Count customer numbers

In a new helper column, enter =IF(A2="","",COUNTIF($A$2:$A$15,A2)) beside the first data row, then fill the formula down. Replace A and the last row number with the customer-number column and final data row in your combined list.

3
Filter repeated records

Select the full data range, including headers, and choose Data > Filter. Open the helper-column filter and select Number Filters > Greater Than, then enter 1.

4
Copy the duplicate rows

Select the visible filtered records across all relevant columns, copy them, and paste them into a new sheet or workbook. Include the headers if needed.

5
Delete the filtered rows

Return to the filtered list, select the visible data row numbers without selecting the header, right-click, and choose Delete Row. Clear the filter from the helper-column dropdown to see the remaining records.

Use COUNTIF and a filter to copy and remove repeated records
All occurrences are removed: Deleting the filtered rows removes every occurrence of a repeated customer number, not just extra copies. Blank customer numbers are excluded by the formula.
Work with customer lists in WPS

Find and remove repeated customer records with WPS Spreadsheet

Use a COUNTIF helper column and a filter in WPS Spreadsheet to copy repeated customer records and remove them from a combined list.

  1. 1. Combine the records: Open the customer lists in WPS Spreadsheet and paste the second list below the first, matching the columns and keeping one header row.
  2. 2. Add a count formula: In a helper column beside the first record, enter =IF(A2="","",COUNTIF($A$2:$A$15,A2)) and fill it down. Adjust the column and last row to cover your customer numbers.
  3. 3. Filter and copy repeats: Select the full data range and choose Data > Filter. Filter the helper column for values greater than 1, then copy the visible records to another sheet or workbook.
  4. 4. Remove the repeated records: Select the filtered data row numbers, right-click, and choose Delete Row. Clear the filter to review the remaining records.
Find and copy all rows with repeated customer numbersOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filters
microsoft office alternative - wps office

Frequently Asked Questions

Does COUNTIF find duplicates within each list as well as across both lists?

Yes. COUNTIF counts each customer number across the entire combined range, so a repeat within either original list is also flagged.

How can I keep one copy of each customer number instead?

After copying any records you need, select the combined data and choose Data > Remove Duplicates in Excel. Select the customer-number column to remove extra occurrences while retaining one row for each number.

Why are some matching customer numbers not being counted together?

Check the customer-number cells for inconsistent text and number formats, leading zeros, or extra spaces. Make the values consistent in both lists before applying COUNTIF.

Can I copy the duplicates to another workbook?

Yes. After filtering the helper column for values greater than 1, copy the visible records and paste them into a sheet in another open workbook.