Data Import & Export

Compare Two Excel Lists for Missing and Duplicate Records

Khadija KhanKhadija Khan Nov 14, 2026 870 views

Question details

Compare two lists using names, email addresses, and birth dates to identify matching, missing, and duplicate records.

Compare Two Excel Lists for Missing and Duplicate Records
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user has two database exports and wants to compare complete records across worksheets.
Observed behavior
The desired result is a list of records that appear in both lists, appear in only one list, or are repeated within a list.
Before you start

Place the lists on worksheets named List1 and List2, with names in column A, email addresses in column B, and birth dates in column C. Ensure dates are stored as dates and remove unwanted spaces or inconsistent spelling before comparing.

Solution 1Recommended

Use COUNTIFS to compare complete records

Use formulas to identify records found in both lists, records unique to either list, and repeated records within a list.

These formulas match all three fields together. They use rows 2 through 1000 as the comparison range; expand the range if either list has more records.

1
Compare List1 with List2

On List1, enter this formula in D2: =IF(COUNTBLANK(A2:C2)>0,"Incomplete",IF(COUNTIFS(List2!$A$2:$A$1000,A2,List2!$B$2:$B$1000,B2,List2!$C$2:$C$1000,C2)>0,"Match","Only in List1"))

2
Compare List2 with List1

On List2, enter this formula in D2: =IF(COUNTBLANK(A2:C2)>0,"Incomplete",IF(COUNTIFS(List1!$A$2:$A$1000,A2,List1!$B$2:$B$1000,B2,List1!$C$2:$C$1000,C2)>0,"Match","Only in List2"))

3
Find repeats within each list

On each worksheet, enter this formula in E2: =IF(COUNTBLANK(A2:C2)>0,"Incomplete",IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2)>1,"Duplicate","Unique"))

4
Fill and filter the results

Drag the fill handle from D2:E2 down to the last record on each worksheet. Select the header row and choose Data > Filter, then filter column D for records found in only one list or column E for duplicates.

Use COUNTIFS to compare complete records
Review incomplete records: Rows with a blank name, email address, or birth date are labeled Incomplete rather than matched. A Match means the same combination exists in the other list; Duplicate means it appears more than once in the current list.
Compare records in WPS Spreadsheet

Find missing and duplicate records with WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use COUNTIFS to compare names, email addresses, and birth dates across two sheets.

  1. 1. Open the workbook: In WPS Spreadsheet, choose Open and select your Excel workbook. Put the lists on sheets named List1 and List2, with names, emails, and birth dates in columns A through C.
  2. 2. Compare both lists: Enter the List1 comparison formula from the solution above in List1 cell D2, and enter the List2 comparison formula in List2 cell D2.
  3. 3. Check for repeated records: Enter the duplicate-check formula from the solution above in E2 on each sheet, then drag D2:E2 down to the last record.
  4. 4. Filter the results: Select the header row and choose Data > Filter. Filter column D for records found in only one list and column E for duplicates.
Compare multiple identifying fields in each recordFilter results to review missing and duplicate recordsOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why should I compare all three fields together?

A name or birth date alone may belong to multiple people. COUNTIFS checks the name, email address, and birth date in the same row as one record.

How can I find records that appear only in the second list?

Use the List2 formula in cell D2, fill it down, and use Data > Filter to show rows labeled Only in List2.

Why does a record show as missing when it looks the same?

Check for extra spaces, different spelling, or birth dates stored as text. Correct the values in columns A through C and let the formulas recalculate.

Does a match between the lists mean there is a duplicate within a list?

No. Match in column D means the record exists in the other list. The formula in column E separately identifies records repeated within the current list.