Compare Two Excel Lists Find New Items

Compare two Excel lists, to find new items in the second list, and add them to the first list. Video shows the steps, and there are written notes and a sample file to download.

Author: Debra Dalgleish

Video: Compare Two Excel Lists

In this video, see how to compare two lists, to see if they have the same entries. Then find all the new items from List 2, and add them to the main list.

Two Lists in Workbook

We need to compare two Excel lists (shown below), and update the Main List. In the sections below, you'll see the steps to finish that task:

• Identify the new items in List 2
• Copy new items to Main List
• Ignore old items in List 2

In this example, the two lists are in the same Excel workbook, on separate worksheets.

Both lists have the same 3 fields in columns A, B and C:

• Email, LastName, FirstName

Create Excel Table

The second list, List 2, is typed on the worksheet, but it isn't set up as a named Excel table, like the Main List.

It will be easier to work with List 2, if it's changed to an Excel Table, so follow these steps to make that change:

• Select any cell in the List 2 data
• On the Excel Ribbon, go to the Home tab
• Click the Format as Table command
• Click on one of the Table Styles, to select it
• In the Create Table dialog box, check that the correct range is entered for the data - cells A3:C43, in the sample file.
• Make sure there is a check mark for the setting, My table has headers.
• Click the OK button, to create a named table for List 2.

Name the List 2 Table

Excel gives each new table a default numbered name, such as Table1.

To give the List 2 table a meaningful name, follow these steps:

• Select any cell in the List 2 table
• On the Excel Ribbon, at the far right, click the Table Design tab
• At the left, in the Table Name box, type a new name, such as NewTable
• Click anywhere on the worksheet, to exit the Table Name box

Note: The Main table has been named as Attendees.

Check for New Records

Some of the records in the new list (NewTable) are already in the Main List.

To check for new records, we'll add a column in the new list, and then enter a formula in that new column.

Add New Column

To add the new column, follow these steps:

• On the ListNew sheet, click in cell D3, beside the FirstName heading.
• Type a heading name - InList
• Press the Enter key, to move down to the next cell
• The table automatically expands, to include the new column

COUNTIF Formula

To check for new items, we'll use the Excel COUNTIF function, to see if each email address is already in the Main List.

The COUNTIF function counts items in a specified range, based on the criteria that you enter in the formula. Its syntax is:     =COUNTIF(range, criteria).

We need to check the Email column in the Main List (range), and see if the Email from List 2 (criteria) is found.

Enter COUNTIF Formula

To enter the formula in the new column, follow these steps:

• Select cell D4
• To start the formula, type this: =COUNTIF(
• Next, go to the Main List, and click at the top of the Email column.
  • That will add the table name and column name to the formula:
    • Attendees[Email]
• Type a comma, and then go back to the NewTable
• Click on cell A4, where the email for the current row is entered
  • That automatically adds the column name to the formula
    • [@Email]
• Type a closing bracket
• Press the Enter key, to enter the completed formula in the cell

The completed formula is: =COUNTIF(Attendees[Email],[@Email])

Note: Because the formula is in a named table, the formula will automatically fill down, to the last row of data.

COUNTIF Formula Results

In the InList column, the results in each row show a 1 or a zero.

• 1: If email address is already in Main List, COUNTIF function found it, and returns a count of 1
• 0: If email address is NOT in Main List, COUNTIF function cannot find it, and returns a count of zero

Any row with a zero in the InList column is a new record, and we need to copy those to the Main List.

Sort Formula Results

To make it easy to copy the records, we can sort the list, so all of the new records are at the top of the Excel table.

Follow these steps to sort the results of the COUNTIF formula, in the InList column:

• In the InList heading cell, click the drop-down arrow
• In the drop-down menu, click the first option - Sort Smallest ot Largest

All of the new records, with zero in the InList column, are sorted to the top of the Excel table.

Copy New Records to Main List

The final step is to copy the new records into the Main List.

To do that, follow these steps:

• In the NewTable data, select the email, last name and first name, for all of the records that have zero in the InList column
• Do not select the formula cells in the InList column
• To copy the selected records, use the keyboard shortcut, Ctrl + C
• Go to the Main List sheet, and go to the end of the Excel table
• Select the cell in column A, in the first blank row below the table
• To paste the copied records, use the keyboard shortcut, Ctrl + V

All of the new records are added to the end of the existing records, and the Excel table automatically expands to include the new rows.

Get the Sample File

Compare 2 Lists -- Compare two lists to find new items, using the COUNTIF function. Format: xlsx  Macros: No  Size: 59 kb  Excel File: Compare Two Excel Lists

_____

More Tutorials

Excel Count Function Examples

Count Criteria in Other Column

Count Cells With Specific Text

Count Specific Items in Cell