How to compare two data sets
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The formula will return TRUE if a record is found in the other data set and FALSE if not.
The image above shows you the first data set: Table1 The image below shows you the second data set: Table2
Excel defined tables has many advantages, one is that you only need to enter a formula in one cell and Excel automatically enters the formula in the remaining cells in the same column.
Another one is that it expands accordingly when new data is added, no need to adjust the cell references. You can also easily filter the data using the arrows in the top row of the Excel table.
How to convert a data set to an Excel defined Table:
Type the following formula in the next column adjacent to the Excel defined Table:
The Excel defined Table will automatically expand shown in the image below and enter the formula in all cells below in that column.
Explaining formula in cell E3
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions. There is a criteria range and a condition forming a pair, our Excel table has three columns so wee need three pairs in order to find matching records in the other table.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Step 1 - Build COUNTIFS function
The first argument is a cell reference to the entire column in table2 and the second argument is a cell reference to the cell on the same row as the formula but in column GivenName. Excel automatically enters these cell references (structured references) if you select the cells with your mouse when you build the formula.
You can also type the table name and column name in brackets, the formula bar will guide you in this process.
COUNTIFS(Table2[GivenName],[@GivenName]
Complete the function by adding the remaining cell references to the COUNTIFS function.
COUNTIFS(Table2[GivenName],[@GivenName],Table2[StreetAddress],[@StreetAddress],Table2[City],[@City])
Step 2 - Logical expression
The COUNTIFS function returns a number representing how many records that match the current record in the other table. A number larger than zero indicates that there is at least one match.
COUNTIFS(Table2[GivenName],[@GivenName],Table2[StreetAddress],[@StreetAddress],Table2[City],[@City])>0
TRUE - At least one match.
FALSE - No match.
Filter records occuring in both tables
- Select sheet 1 (Table1)
- Press with left mouse button on black arrow in column Compare
- Disable FALSE
- Press with left mouse button on OK.
The image below shows a filtered table with records that exists in both tables.
Filter records existing in only one table
- Select a sheet
- Press with left mouse button on black arrow in column Compare
- Enable only FALSE
- Press with left mouse button on OK.
Table1
Table2
Table category
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Functions in this article
More than 1300 Excel formulas
How to use Excel Tables
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.