## 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 you an array formula that extracts records not shared by both data sets.

### What's on this page

Let me demonstrate how to extract non-common records from two tables.

The formulas in this post contain the COUNTIFS function and were 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 an array formula.

### Example 1 - Excel defined tables

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 - Extract records using an array formula

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.

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

- Press with left mouse button on black arrow near header "Common"
- Deselect True
- Press with left mouse button on Ok
- Press with left mouse button on
- Press with left mouse button on black arrow near header "Region"
- Deselect all values except "Asia"
- Press with left mouse button on OK

### Table category

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

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

### Records category

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

## How to use Excel Tables

### 7 Responses to “Remove common records between two data sets”

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

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!

HI Oscar, I have almost similar data but in one sheet, seperate with some blank rows, i am trying to extract unique row by comparing both tables but not getting the answer.

Moreover, might possible both table wouldn't have same range, shall thi affect the formula...? TIA

Here is th formula I am trying..

=IFERROR(IFERR0R(INDEX($A$2:$B$7, SMALL(IF(COUNTIFS($A$2:$A$7, $A$11:$A$16, $B$2:$B$7, $B$11:$B$16)=0, MATCH(ROW($A$19:$A$26), ROW($A$19:$A$26)), ""), ROW(A18)), COLUMN(A18)), INDEX($A$11:$B$16, SMALL(IF(COUNTIFS($A$11:$A$16,$A$2:$A$7, $B$11:$B$16,$B$2:$B$7)=0, MATCH(ROW($A$19:$A$26),ROW($A$19:$A$26)), ""), ROW(A18)-SUM(--(COUNTIFS($A$2:$A$7,$A$11:$A$16, $B$2:$B$7,$B$11:$B$16)=0))), COLUMN(A18))), "")

KK,

The following article explains how to extract unique distinct records from two tables:

https://www.get-digital-help.com/2018/06/11/extract-unique-distinct-records-from-two-data-sets/