# How to extract not shared values in two columns

**Question:** How do i remove common values between two lists?

**Answer:** The solution in this article, removes common values and the original values, as seen on the picture above.

Array formula in cell F3:

To enter an array formula, type the formula in cell B3 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, they appear automatically.

### Explaining formula in cell F3

#### Step 1 - Count values

The COUNTIF function lets you count values based on a condition or criteria.Â COUNTIF(*range*,Â *criteria*) The first argument is the cell range you want to evaluate.

The criteria argument contains the values you want to look for.

COUNTIF($D$3:$D$12,$B$3:$B$12)

becomes

COUNTIF({"AA"; "BB"; "VV"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"},{"AA"; "BB"; "CC"; "DD"; "EE"; "PP"; "GG"; "HH"; "II"; "JJ"})

and returns the following array {1;1;0;1;1;0;1;1;1;1}.

The image to the right shows the array and the corresponding values, you can now easily see that CC and PP are not found in List 2.

#### Step 2 - Check if value is 0 (zero) and if so return the corresponding row number

If the array contains a zero the corresponding value is not found in the other list. The IF function lets you build a logical expression, if it evaluatesÂ to TRUE on thing happens and if FALSE another thing happens.

IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), "")

becomes

IF({1;1;0;1;1;0;1;1;1;1}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns the following array:

{"";"";3;"";"";6;"";"";"";""}

The image to the right shows the row numbers (relative to cell rangeÂ $B$3:$B$12) in column C .

#### Step 3 - Extract the k-th smallest row number

The SMALL function returns the k-thÂ smallest number from a cell range or an array. This allows us to extract a single value in a cell each.

SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, ROWS($A$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, 1)

and returns 3.

#### Step 4 - Get the corresponding value

The INDEX function returns a value based on a row and column number, the column number is not necessaryÂ if the cell range only has one column.

INDEX($B$3:$B$12, SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$12, 3)

and returns the value in cell B5 which is "CC" to cell F3.

#### Step 5 - Return values from List 2

When there are no more values to extract from List 1 the IFERROR function lets you continue with List 2.

The formula is repeated in the second argument except that other cell ranges are used. I am not going to explain these steps again, see above steps if you need to.

The fomula in cell F5 becomes

IFERROR(INDEX($B$3:$B$12, SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0,ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1,""),ROW(B1))),INDEX($D$3:$D$12, SMALL(IF(COUNTIF($B$3:$B$12,$D$3:$D$12)=0, ROW($D$3:$D$12)-MIN(ROW($D$3:$D$12))+1, ""), ROW(B1)-SUM((COUNTIF($D$3:$D$12,$B$3:$B$12)=0)+0))))

becomes

IFERROR(#NUM,INDEX($D$3:$D$12, SMALL(IF(COUNTIF($B$3:$B$12,$D$3:$D$12)=0, ROW($D$3:$D$12)-MIN(ROW($D$3:$D$12))+1, ""), ROW(B1)-SUM((COUNTIF($D$3:$D$12,$B$3:$B$12)=0)+0))))

becomes

=IFERROR(#NUM,INDEX($D$3:$D$12, 3))

and returns "VV" in cell F5.

### Get Excel *.xlsx file

remove-common-values-between-two-columns3.xlsx

To create a unique list from two columns or two cell ranges, check this article out: Create unique list from two columns

### Compare category

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

### Excel categories

### 2 Responses to “How to extract not shared values in two columns”

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

[...] have a look at this link ad eventually the related pages __________________ Happy with the answer ? Press with left mouse button on the scale [...]

The above formulas are very much informative.How I can fix the formula to extract not shared values of the first column which need to fix the corresponding raw of the 1st column Column.

Data 1 Data 2 Required Result Formula

1 2 1 1

5 5 7

7 9 7 11

9 16 11

9 25

11 11 25

11 11 33

16 35

16 58

25 25 60

25 25 63

33 33 69

35 35 73

58 58 93

60 60 97

63 63

69 69

73 73

93 93

97 97