## 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 modify the countifs function if your columns are not ordered.

This is the first example list on sheet: List 1

Sheet: List 2

### Create named ranges

There is really no need for named ranges in this example, although they shorten array formulas considerably.

- Select A2:A13 on sheet "List 2"
- Type Year in Name Box
- Press Enter

Repeat with remaining ranges:

Sheet: List 2 , Range:B2:B13, Name: Asset

Sheet: List 2 , Range:C2:C13, Name: Cost

Sheet: List 1 , Range:A2:A11, Name: Year1

Sheet: List 1 , Range:B2:B11, Name: Asset1

Sheet: List 1 , Range:C2:C11, Name: Cost1

### Filter records existing in only one list, excel 2007

**Array formula in cell A4:**

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

**Array formula in cell A14:**

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

### Explaining the array formula in cell A4

*Step 1 - Find records existing only in List 1*

Let us take a look at the bolded part of the formula.

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(**COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)=0**, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)

becomes

COUNTIFS({1997, 1998, 1998, 1999, 2001, 2002, 2002, 2002, 2002, 2003, 2003, 2004},{1997, 1998, 1998, 1999, 2001, 2002, 2002, 2002, 1996, 2002}, {Printer, Press, Copier, Copier, Printer, Copier, Printer, Computer, Lift, Vacuum, Press, Copier},{Printer, Press, Copier, Copier, Printer, Copier, Printer, Computer, Computer, Copier},{2025, 11000, 1575, 1199, 825, 1231, 1788, 17090, 15464, 359, 6900, 799},{2025, 11000, 1575, 1299, 825, 1231, 1788, 17090, 15275, 1577})

and returns this array: {1, 1, 1, 0, 1, 1, 1, 1, 0, 0}

COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)=0

becomes

{1, 1, 1, 0, 1, 1, 1, 1, 0, 0}=0

and returns

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}

*Step 2 - Convert boolean array to row numbers*

IF(COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1)

becomes

IF({FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, ROW(Year1)-MIN(ROW(Year1))+1)

becomes

IF({FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10})

and returns

{FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}

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

SMALL(IF(COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1))

becomes

SMALL({FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}, ROW(A1))

becomes

SMALL({FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}, 1)

and returns 4.

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

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS(Year, Year1, Asset, Asset1, Cost, Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, 1)

and returns 1999.

### Download excel sample file for this tutorial.

Filter records existing in only one list.xlsx

(Excel 2007 Workbook *.xlsx)

### Functions used in article:

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

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

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

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

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

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

**ROW(**reference**)**

Returns the rownumber of a reference

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

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

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

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

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

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, 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