This blog post demonstrates 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.

Sheet1

Sheet2

Count records using COUNTIFS function

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

   in cell D2.

3. 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 

1. Click on black arrow
   
2. Deselect 1.
3. Click OK.

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

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
   
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. 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.