Compare Two Excel Lists for Missing and Duplicate Records
Question details
Compare two lists using names, email addresses, and birth dates to identify matching, 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.
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.
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.
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"))
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"))
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"))
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.

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. 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. 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. 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. 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.

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.




