Home > Formulas > Count > Compare

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. My video shows the steps, and there are written notes and a sample file to download.

Note: To check for exact matches in a list, including upper and lower case, go to the Compare Cell Values page. There are EXACT function examples, written steps, a video, and sample file.

COUNTIF formula results 1 or zero

Author: Debra Dalgleish

Video: Compare Two Excel Lists

In this video, see how to compare two lists in Microsoft Excel using formulas to see if they have the same entries. Then find all the new items from List 2, and add them to the main list, without creating duplicate values. The written steps, and the video transcript, are below the video.

Video Timeline

  • 00:00 Introduction
  • 00:15 Two Lists
  • 00:45 Excel Table
  • 01:30 Find New Records
  • 02:42 Add New Records
  • 03:30 Get the Sample File

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Video Transcript: Compare Two Excel Lists

Here is the full transcript for the Compare Tow Excel Lists video.

Introduction

We've got a list of names and email addresses. And we just got a new list that has some of those same names and some new ones.

We're going to find the new ones and get them into our list. This is Debra Dalgleish from Contextures.com.

Two Lists

Here's our old list. It's in a named Excel table. We've got email, last name, first name, and whether or not this person will attend our conference.

The email is unique. No two people are going to have that same email address.

We've got a new list, which also has email. And first and last names.

We're going to check the emails and see if they're already in our list. And that will keep us from getting duplicates.

Excel Table

Before I start to work with this list, I'm going to change it to a named Excel table, which will make it easier to select things.

  • So select any cell in the list.
  • On the Home tab, click Format as Table and choose a style you like. So I'll go with a black heading.
  • Where is the data? It's selected the correct range.
  • My table does have headers, so I'll leave that checked, and click OK.

If any cell in the table is selected, there's a table Design tab on the ribbon.

  • If I click that, I can see that this was given a default name.
  • I'm going to change this to NewTable.

So going back to this table, this is called Attendees

Find New Records

To find the new items in this list, I'm going to check the emails and see if they're already in the Attendees table.

And to do that, I'm going to add a column here and I'll type a heading here, InList. Is this email in that other list?

And to do that, I'm going to do a count, I'll count the number of times this email address is in the Attendees table.

And the function that will do that for us is COUNTIF

  • I'll start by typing =COUNTIF, an open bracket.
  • What it wants is a range.
    • So it's going to check somewhere, and where we want it to check is the email column in our Attendees table, so I'll go there
  • .And when I click at the top of that column, it's got the table name and the column name, and that's what we want it to check
  • Type a comma
  • Going back to the new list, what we want it to check is this email cell in each row.
  • So when I click on that, now it's showing the field name in this table, and that's what it's going to check
  • Close the bracket and press Enter.

Add New Records

In this column, the formula either shows a one or a zero.

  • If it found the email address, it's a one.
  • If it did not find it, we have a zero.

We want to copy anything that has a zero.

And to make that easy, I'm going to sort the column and then select all the zeros.

  • To sort, click the arrow in the heading and sort smallest to largest.

So all the zeros are at the top where we can easily copy them

  • Drag to select
  • Don't copy that formula column
  • Select all the names
  • Then Ctrl + C
  • Then go to the old list
  • Go to the bottom of the list, end and down arrow.
  • And then one more down arrow
  • And press Ctrl + V to paste.

And now all the new names are in the 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

two Excel lists in workbook

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.

change List 2 to named Excel table

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.

change name for Excel table

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, to count any matching data.

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

add new column in Excel table

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 of cells, 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. If the formula does NOT fill down, point to the Fill Handle at the bottom right corner of cell D4, and double-click.

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.

COUNTIF formula results 1 or zero

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

change name for 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 InList formula cells in column D
  • 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 at the end of the existing records, starting in the first column. The Excel table automatically expands to include the new rows.

new records pasted at end of main list

Compare Lists - FILTER Function

In Excel 365, you can use the new FILTER function to compare lists.

You can use it to filter out the items from the second list that are not in the first list. Then, copy the formula results, and paste at the end of the first list, as values.

This example has two lists with email addresses and names, just like the COUNTIFS example, in the previous sections.

Instead of adding a new column with COUNTIFS formulas, this example only needs one cell with a FILTER formula..

FILTER Function Formula

The FILTER formula will find names from the new list, which is in a table named MoreNames. In the formula:

  • COUNTIF function checks the main table, named Attendees, to see if the email is found there.
  • If email is NOT found (count = 0), FILTER function includes that email in New Names list.

To create the list of new names, follow these steps:

  • On the ListNew sheet, enter the following formula in cell E4
    • =FILTER(MoreNames, COUNTIF(Attendees[Email], MoreNames[Email])=0, "No New Names")
  • Press the Enter key, to see the list of new names, spread over 3 columns

FILTER function extracts list of new emails

Add New Names to Main List

Next, follow these steps, to add the new names to the main list, on the ListOld sheet:

  • Select cell E4, where the FILTER formula was entered
  • Press the keyboard shortcut, Ctrl + A, to select the entire list of new names
  • Next, press the keyboard shortcut, Ctrl + C, to copy the selected cells
  • Go to the ListOld sheet
  • In column A, right-click in the first blank row below the old list -- cell A37 in this example
  • In the pop-up menu, in the Paste Options section, click Values
  • Paste Options Values

The new names are added, and the Attendees table expands automatically, to include rows with the new names.

Check the FILTER Formula

After adding the names, go back to the ListNew sheet. Now the formula cell shows the result, "No New Names", because all of the new list emails have been added to the main list.

  • The 3rd argument in the FILTER function syntax is [if_empty].
  • Use this argument to show a helpful text message, instead of an error value, such as #CALC!

In this example, the if_empty argument was set as "No New Names"

  • =FILTER(MoreNames, COUNTIF(Attendees[Email], MoreNames[Email])=0, "No New Names")

FILTER function extracts list of new emails

Get the Sample File

FN0063

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

   
FN0063

FILTER New Names -- Use FILTER function to find new items, in Excel 365 or Excel for Web.
Format: xlsx  Macros: No 
Excel File: FILTER function-Compare Two Lists

_____

More Tutorials

Compare Cell Values

Excel Count Function Examples

Count Criteria in Other Column

Count Cells With Specific Text

Count Specific Items in Cell

 

 

Last updated: June 22, 2023 11:06 AM