Quickly compare two tables in excel 2007
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
- Select sheet 1 (Table1)
- Click black arrow in column Compare
- Disable FALSE
- Click OK.
Records existing 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
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
Related posts:
Compare two tables in excel: Remove common rows
Applying conditional formatting to related tables
Compare pricelists in excel 2007
Quickly create links to sheets, tables, pivot tables and named ranges in a workbook
Recommended categories
