## 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: List 2

### Comparing the two lists

We need to know what columns to compare before we can create the array formula.

In this example:

List 1:Column A - List 2: Column E

List 1:Column B - List 2: Column B

List 1:Column C - List 2: Column D

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

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

COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)

With the countifs function complete we can now construct the array formula.

### Array formula

Sheet: Common records

Array formula in cell A2:

Copy cell A2 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 A2

*Step 1 - Find common records*

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(**COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)**>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1))

COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)

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}

*Step 2 - Identify row numbers*

=INDEX('List 1'!$A$2:$C$11, SMALL(**IF(COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)**, ROW(A1)), COLUMN(A1))

IF(COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

IF({1, 1, 1, 0, 1, 1, 1, 1, 0, 0}>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

IF({TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE}, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

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

and returns {1,2,3,FALSE,5,6,7,FALSE,FALSE}

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

SMALL(IF(COUNTIFS('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1))

becomes

SMALL({1,2,3,FALSE,5,6,7,FALSE,FALSE}, ROW(A1))

becomes

SMALL({1,2,3,FALSE,5,6,7,FALSE,FALSE}, 1)

and returns 1.

*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('List 2'!$E$2:$E$13, 'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1))

becomes

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

becomes

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

and returns 1997.

### Download excel sample file for this tutorial.

Compare lists.xlsx

(Excel 2007 Workbook *.xlsx)

### Functions used in this formula:

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

### Related Posts

- Prevent duplicate records (data validation)
- Quickly compare two tables in excel 2007
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Highlight records existing in only one list in excel
- Compare two lists of data: Highlight common records in excel

### 7 Responses to “Compare two lists of data: Filter common row records in excel”

### Leave a Reply

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

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

your 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

Oscar,

As usual, another great article. Just wanted to say thanks for all the awesome articles you publish. I always learn something from your work--it is appreciated.

Michael Pennington,

Thank you for commenting!!

[...] Recommended blog posts Compare two lists of data: Filter common row records in excel [...]

[...] blog post: Compare two lists of data: Filter common row records in excel Related posts:Excel udf: Filter values existing only in one out of two [...]

More complicated if we have 3 columns to compare the duplicates, you have the sample cases or formula for 3 column to retrieve duplicates?

Thanks

Rizky,

The example in this post compares 3 columns. Can you explain in greater detail?

Im sorry not inform clear to you, I mean compare 3 list, and is it possible retrieve common records with criteria? For example I want to retrieve common records between 2 or more list with criteria year 1997?