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