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