How to Merge Excel Spreadsheets to Find Duplicates

Complete the Merge
1. Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.
2. Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first four columns of both spreadsheets are First Name, Last Name, Phone and Email. Make sure there are no empty columns or rows.
3. Copy and paste or consolidate. Copy the information from spreadsheet A and paste the information into spreadsheet B below the last record on spreadsheet B. Each column in spreadsheet B should line up with the appropriate column in spreadsheet A.
Identify Duplicate Records
4. Insert a new column. Insert the new column next to the column with the duplicate value. For example, if you are looking for duplicate phone numbers in column C, insert a new column to the left of column C. Column C should be come before column D. Type the title 'Duplicate?' into the column's first row to name the column.
5. Enter the formula for identifying one column duplicates. Select the second row of your 'Duplicate?' column and enter the duplicate formula: =IF(D2=D3, 'Duplicate',''). This formula will identify duplicate records in column D only. For duplicates in multiple columns, enter =IF(D2E2=D3E3, 'Duplicate',''). This formula will identify duplicate records in columns D and E.
6. Copy and paste the formula. Copy the formula down the length of the appropriate column. Right-click the cell with the formula and select 'Copy' from the short-cut menu. Select each cell down the length of the appropriate column and right-click and select 'Paste Special...', then click 'Values' and 'OK.'
7. Sort the 'Duplicates?' column. Sort your spreadsheet based on the duplicate column. Sort the duplicate column in descending order to move each duplicate to the top of the list.