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
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.
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:
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:
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:
Excel gives each new table a default numbered name, such as Table1.
To give the List 2 table a meaningful name, follow these steps:
Note: The Main table has been named as Attendees.
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 add the new column, follow these steps:
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.
To enter the formula in the new column, follow these steps:
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.
In the InList column, the results in each row show a 1 or a zero.
Any row with a zero in the InList column is a new record, and we need to copy those to the Main List.
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:
All of the new records, with zero in the InList column, are sorted to the top of the Excel table.
The final step is to copy the new records into the Main List.
To do that, follow these steps:
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.
Compare 2 Lists -- Compare two lists to find new items, using the COUNTIF function.
Last updated: June 20, 2022 8:28 PM