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

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 […]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 […]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 […]