## 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)
- Click black arrow in column
*Compare* - Disable
*FALSE* - Click 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
- Click black arrow in column
*Compare* - Enable only FALSE
- Click OK.

**Table1**

**Table2**

### Download Excel file

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

Remove common records between two data sets

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

Copy filtered Excel tables [VBA]

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]

Highlight duplicates in a filtered Excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

Copy excel table filter criteria [VBA]

Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can expand […]

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

**Contact Oscar**

You can contact me through this contact form