## Compare two tables in excel: Remove common rows

**Table of Contents**

Let me demonstrate how to extract non common records from two tables. The formulas in this post contains the COUNTIFS function and is introduced in excel 2007.

The first example shows you how to hide common rows in a table. The second example describes how to compare two tables and extract not common rows/records using array formula.

### Example 1,

Sheet 1 and sheet 2 contains random data. As you can see, row 1 and 3 are common records between the two tables.

**Count records using COUNTIFS function**

- Select cell D2 in sheet1.
- Type
=COUNTIFS(Sheet2!$A$2:$A$4, A2, Sheet2!$B$2:$B$4, B2, Sheet2!$C$2:$C$4, C2)
in cell D2.

- Press Enter

The formula is instantly copied to all table cells in column D. The countifs function counts common records from two tables. Row 2 in sheet1 is found once in sheet 2 and so on.

**Hide common rows **

Common records are removed.

Repeat steps in **Count records using COUNTIFS function **and** Hide common rows **with sheet2.

### Example 2,

This example describes how to compare two tables and extract not common rows/records using an array formula.

Sheet1

Sheet2

Compare sheet

**Array formula in cell A2:**

This array formula may look complicated but it is not. It contains two different formulas. If the first formula returns an error, the second formula is calculated. The first formula extracts not common rows from the first table. The second formula extracts not common rows from the second table.

You can add a second IFERROR() function to the formula to remove #num errors.

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### Explaining formula

This formula is the first formula. It extracts not common rows from the first table. The second formula is exactly the same but cell references are pointing to the second table.

**Step 1 - Identify common records**

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)

Counts the number of cells specified by a given set of conditions or criteria

COUNTIFS(Sheet1!$A$2:$A$4, Sheet2!$A$2:$A$4, Sheet1!$B$2:$B$4, Sheet2!$B$2:$B$4, Sheet1!$C$2:$C$4, Sheet2!$C$2:$C$4)=0

becomes

COUNTIFS({"Martin";"Laura";"Margeret"}, {"Martin";"Ben";"Margeret"}, {"Anchorage";"New York";"London"}, {"Anchorage";"Los Angeles";"London"}, {26;28;25}, {26;28;25})=0

becomes

{1;0;1}=0 and returns {FALSE; TRUE; FALSE}

**Step 2 - Convert array into row numbers**

IF(COUNTIFS(Sheet1!$A$2:$A$4, Sheet2!$A$2:$A$4, Sheet1!$B$2:$B$4, Sheet2!$B$2:$B$4, Sheet1!$C$2:$C$4, Sheet2!$C$2:$C$4)=0, MATCH(ROW($A$2:$A$4), ROW($A$2:$A$4)), "")

becomes

IF({FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$4), ROW($A$2:$A$4)), "")

becomes

IF({FALSE; TRUE; FALSE}, MATCH({2;3;4}, {2;3;4}), "")

becomes

IF({FALSE; TRUE; FALSE}, {1;2;3}), "")

and returns

{"";2;""}

**Step 3 - Return the k-th smallest number**

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(IF(COUNTIFS(Sheet1!$A$2:$A$4, Sheet2!$A$2:$A$4, Sheet1!$B$2:$B$4, Sheet2!$B$2:$B$4, Sheet1!$C$2:$C$4, Sheet2!$C$2:$C$4)=0, MATCH(ROW($A$2:$A$4), ROW($A$2:$A$4)), ""), ROW(A1))

becomes

SMALL({"";2;""}, ROW(A1))

becomes

SMALL({"";2;""}, 1)

and returns 2.

**Step 4 - Return a value of the cell at the intersection of a particular row and column**

INDEX(array,row_num,[column_num])

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX(Sheet1!$A$2:$C$4, SMALL(IF(COUNTIFS(Sheet1!$A$2:$A$4, Sheet2!$A$2:$A$4, Sheet1!$B$2:$B$4, Sheet2!$B$2:$B$4, Sheet1!$C$2:$C$4, Sheet2!$C$2:$C$4)=0, MATCH(ROW($A$2:$A$4), ROW($A$2:$A$4)), ""), ROW(A1)), COLUMN(A1))

becomes

INDEX(Sheet1!$A$2:$C$4, 2, COLUMN(A1))

becomes

INDEX(Sheet1!$A$2:$C$4, 2, 1)

becomes

INDEX({"Martin", "Anchorage",26;"Laura", "New York", 28;"Margeret", "London", 25}, 2, 1)

and returns "Laura" in cell A2.

### Download example file

remove common records.xlsx

Excel workbook 2007 *.xlsx

### Functions in this post:

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k**)**

Returns the k-th smallest number in this data set.

## Compare two tables using a condition

There are two tables on this sheet. The array formula extracts common records in the two tables using a condition. There can be numerous occasions where this can be useful, for example comparing values on the same year or maybe year and month. The picture below shows common names in region Asia.

**Array formula in cell B14:**

**How to enter an array formula**

There are now curly brackets surrounding the formula in the formula bar.

### Defined tables

This example demonstrates how to filter not common values in region Asia using defined tables. I converted the two tables to excel defined tables.

**Formula in cell D3:**

=COUNTIFS(Table2[[Region ]],B3,Table2[Name],C3)>0

**Formula in cell H3:**

=COUNTIFS(Table1[Region],F3,Table1[Name],G3)>0

**Filter values**

- Click black arrow near header "Common"
- Deselect True
- Click Ok
- Click
- Click black arrow near header "Region"
- Deselect all values except "Asia"
- Click OK

### Download excel *.xlsx file

### Category: Compare

How to find common values from two lists

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]Comments(13) Filed in category: Compare, Excel

Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]Comments(7) Filed in category: Compare, Excel

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Comments(5) Filed in category: Compare, Excel, Unique distinct values

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]Comments(5) Filed in category: Compare, Excel

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Filter common values from three columns in excel

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]Comments(4) Filed in category: Compare, Excel

Compare and filter values between two lists in excel

Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. […]Comments(4) Filed in category: Compare, Excel

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]Comments(3) Filed in category: Compare, Excel

### Category: Records

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Quickly highlight records in a list using multiple criteria in excel

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]Comments(3) Filed in category: Conditional formatting, Excel, Records

Compare two lists of data: Highlight common records in excel

Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and […]Comments(2) Filed in category: Compare, Excel, Records

Compare two lists of data: Highlight records existing in only one list in excel

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]Comments(1) Filed in category: Compare, Excel, Records

Quickly highlight records in a list in excel (AND logic)

This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change […]Comments(0) Filed in category: Conditional formatting, Excel, Records

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 […]Comments(0) Filed in category: Compare, Excel, Records

How to sort a table in a custom order in excel [No formula]

Your boss wants you to sort the company´s products by a new criterion, quality. You receive a list from your […]Comments(0) Filed in category: Excel, Records, Sort values

Filter and highlight duplicate column records in excel 2007

This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell […]Comments(0) Filed in category: Conditional formatting, Excel, Records

Compare two lists of data: Filter records existing in only one list in excel

In this example we are going to use two lists with identical columns in excel 2007. It is easy to […]Comments(0) Filed in category: Compare, Excel, Records

### 5 Responses to “Compare two tables in excel: Remove common rows”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

This is really quality info. Thanks Oscar!!

Julián Fernández,

Thank you for commenting!

Really its super. Thank you !!

Example 1 was more helpful and easy way to identify common and uncommon records... This was really helpful. Thanks a lot !

krishna and Chamundeswari,

Thank you for commenting!