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 noncommon 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 kth smallest number
SMALL(array,k) returns the kth 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
 Click black arrow near header "Common"
 Deselect True
 Click Ok
 Click
 Click black arrow near header "Region"
 Deselect all values except "Asia"
 Click OK
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to [โฆ]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]
Count unique distinct values in a filtered Excel defined Table
A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a [โฆ]
Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am [โฆ]
Highlight duplicates in a filtered Excel defined table
You can highlight duplicates in an excel defined table using conditional formatting. However, that wonยดt work if you only want [โฆ]
Copy filtered Excel tables [VBA]
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is [โฆ]
Copy excel table filter criteria [VBA]
Here is how to copy filter criteria from an excel table and use the same table filters on another table. [โฆ]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
Quickly search a data set with many criteria
Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost [โฆ]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in [โฆ]
Unique distinct records sorted based on count or frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on [โฆ]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The [โฆ]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two [โฆ]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. [โฆ]
How to sort a data set in a custom order
Your boss wants you to sort the company's products by a new criterion, quality. You receive a list from your [โฆ]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered [โฆ]
Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is [โฆ]
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.getdigitalhelp.com/2018/06/11/extractuniquedistinctrecordsfromtwodatasets/