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

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

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

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

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

Comments(5) Filed in category: Compare, Excel, Unique distinct values

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

### Category: Records

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a […]

Comments(143) Filed in category: Excel, Records

Question: Does anyone know how to do a vlookup of three columns to pull a single record? Answer: I am […]

Comments(10) Filed in category: Excel, Records, Vlookup

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

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

Formula in cell G3: =IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "") + ENTER Copy cell G3 and […]

Comments(3) Filed in category: Duplicate values, Excel, Records