Compare two columns and return differences
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) and not in List 2 (D3:D8). The same formula is used in cell B15, however, with different cell references. This time it extracts values that only exist in List 2 (D3:D8).
Array Formula in B11:
To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell D2 and paste it down as far as needed.
Array Formula in B15:
Copy cell D9 and paste it down as far as needed.
Explaining formula in cell B11
Step 1 - Count values in List 1 based on values in List 1
The COUNTIF function counts values based on a condition or criteria.
COUNTIF($D$3:$D$8, $B$3:$B$7)=0
becomes
COUNTIF({"D"; "A"; "G"; "Z"; "I"; "C"}, {"A"; "E"; "D"; "Z"; "F"})=0
becomes
{1;0;1;1;0}=0
The logical expression returns TRUE if a value in the array is equal to 0 (zero).
{1;0;1;1;0}=0
returns {FALSE; TRUE; FALSE; FALSE; TRUE}
Step 2 - Replace TRUE with corresponding row number
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(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; TRUE}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5}, "")
and returns
{""; 2; ""; ""; 5}
Step 3 - Extract k-th smallest row number
To be able to return a new value in a cell each I use the SMALL function to filter row numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand automatically when the cell is copied to the cells below.
SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1))
becomes
SMALL({""; 2; ""; ""; 5}, ROWS($A$1:A1))
becomes
SMALL({""; 2; ""; ""; 5}, 1)
and returns 2.
Step 4 -
The INDEX function returns a value based on a cell reference and a row number (also a column number if needed).
INDEX($D$3:$D$8, SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1)))
becomes
INDEX($D$3:$D$8, 2)
and returns "E" in cell B11.
Sorted from A to Z
Array Formula in B11:
Array Formula in B15:
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? […]
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 a formula that extracts values that exist only in one column out of two columns. There are […]
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 […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Functions in this article
More than 600 Excel formulas
Excel categories
5 Responses to “Compare two columns and return differences”
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.
Oscar here's another variation to this, a much smaller one I guess.
Values in List 1 and not in List 2
=INDEX(List_1, SMALL(IF(ISERROR(1/COUNTIF(List_2, List_1)), ROW(List_1)-MIN(ROW(List_1))+1), ROWS($A$2:A2)))
Chrisham,
Your array formula is great! My array formula creates unique distinct values, that is perhaps not what he asked for.
Thanks a lot for your contribution!!
Guys,
Realy appreciate your contribution.
I need the unique distinct value.
I'll keep both proposed solution. Always interresting to test and compare :-).
Again, thanks a lot.
I have been looking for something similar to this. I am trying to automate a billing process and need some help with it.
Thanks, that was exactly what I needed.