## Compare two lists of data: Filter records occurring in only one list

*Article updated on March 12, 2018*

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

Compare two lists of data: Highlight records occurring in only one list

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article