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:
Compare two lists of data: Filter records existing in only one list in excel
Compare two lists of data: Highlight common records in excel
Compare two lists of data: Highlight records existing in only one list in excel
Recommended categories
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 [...]