## Filter values occurring in range 1 but not in range 2

The formulas above extracts values that exists only in one or the other cell range, if you are looking for a formula that compares two separate columns the go here: Compare two columns and show differences

Array formula in B13:

Array formula in B22:

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.

If you don't own an Excel version that has the TEXTJOIN function then use the following considerably larger formula.

Array formula in B13:

copied down as far as necessary.

Formula in B21:

copied down as far as necessary.

### Explaining formula in cell B13

#### Step 1 - Identify values not shared by the ranges

The COUNTIF function counts values based on a condition or criteria, in this case, it counts values between the cell ranges.

COUNTIF($B$7:$E$10, $B$2:$D$4)=0

becomes

COUNTIF({"HH", "II", "JJ", "KK";"AA", "MM", "NN", "OO";"AA", "QQ", "RR", "SS";"TT", "II", "VV", "CC"}, {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})=0

becomes

{2,0,1;0,0,0;0,1,0}=0

and returns

{FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}.

#### Step 2 - Prevent duplicates in list

COUNTIF(B12:$B$12, $B$2:$D$4)

becomes

COUNTIF("Values existing in range One but not in range Two", {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})

and returns

{0,0,0;0,0,0;0,0,0}.

#### Step 3 - Add arrays

Check if array is equal to 1.

((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1

becomes

({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}+{0,0,0;0,0,0;0,0,0})=1

becomes

{0,1,0;1,1,1;1,0,1}=1

and returns

{FALSE,TRUE,FALSE;TRUE,TRUE,TRUE;TRUE,FALSE,TRUE}.

#### Step 4 - Convert TRUE to a unique number

IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, "")

becomes

IF({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, "")

becomes

IF({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}, {2.33333333333333, 2.25, 2.2;3.33333333333333, 3.25, 3.2;4.33333333333333, 4.25, 4.2}, "")

and returns

{"", 2.25, "";3.33333333333333, 3.25, 3.2;4.33333333333333, "", 4.2}

#### Step 5 - Find smallest number in array

The MIN function returns the minimum number in the given array.

MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))

becomes

MIN({"", 2.25, "";3.33333333333333, 3.25, 3.2;4.33333333333333, "", 4.2})

and returns 2.25

#### Step 6 - Find position in array

IF(MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

becomes

IF(2.25=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

becomes

IF(2.25={2.33333333333333, 2.25, 2.2;3.33333333333333, 3.25, 3.2;4.33333333333333, 4.25, 4.2}, $B$2:$D$4, "")

and returns {"","BB","";"","","";"","",""}.

#### Step 7 - Concatenate strings in array

The TEXTJOIN function returns values concatenated ignoring blanks in array.

TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, ""))

becomes

TEXTJOIN("", TRUE, {"","BB","";"","","";"","",""})

and returns "BB" in cell B13.

### Download Excel *.xlsx file

Extract shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three separate columns

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 […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

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

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Automate Excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

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) […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

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

**Contact Oscar**

You can contact me through this contact form