Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is easy to modify the COUNTIFS function if your columns are not arranged in the same order, see the COUNTIFS explanation below.
Create named ranges
There is really no need for named ranges in this example, although they shorten array formulas considerably. You can skip this step if you want.
- 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
Array formula in cell A4:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
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
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
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
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
The ROW function keeps track of the numbers based on a relative cell reference. It will change as the formula is copied to the cells below.
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
The INDEX function returns a value based on a cell reference and column/row numbers.
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.
Compare category
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Records category
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Your boss wants you to sort the company's products by a new criterion, quality. You receive a list from your […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.