This blog post demonstrates how to quickly compare two tables in excel 2007.

Table1

Table2

Create a fourth column and use this formula in first cell:

Table1

=COUNTIFS(Table2[GivenName], Table1[[#This Row], [GivenName]], Table2[StreetAddress], Table1[[#This Row], [StreetAddress]], Table2[City], Table1[[#This Row], [City]])>0

This is how the formula looks like with cell references:

=COUNTIFS(Sheet2!$B$3:$B$17, Sheet1!$B3, Sheet2!$C$3:$C$17, Sheet1!$C3, Sheet2!$D$3:$D$17, Sheet1!$D3)>0

Copy cell E3 and paste down.

Table2

=COUNTIFS(Table1[GivenName], Table2[[#This Row], [GivenName]], Table1[StreetAddress], Table2[[#This Row], [StreetAddress]], Table1[City], Table2[[#This Row], [City]])>0

This is how the formula looks like with cell references:

=COUNTIFS(Sheet1!$B$3:$B$17, Sheet2!$B3, Sheet1!$C$3:$C$17, Sheet2!$C3, Sheet1!$D$3:$D$17, Sheet2!$D3)>0

Copy cell E3 and paste down.

Filter records existing in both tables

  1. Select sheet 1 (Table1)
  2. Click black arrow in column Compare
  3. Disable FALSE
  4. Click OK.

Records existing in both tables.

Filter records existing in only one table

  1. Select a sheet
  2. Click black arrow in column Compare
  3. Enable only FALSE
  4. Click OK.

Table1

Table2

Download excel file

Quickly compare two tables.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria