Non-intersecting values in columns in Excel

Question

I have three columns with values (A, B, C) as shown below. I would like a formula in column D that will return ALL the values from column B that are not listed in column A. Similarly, what formula should I write for column E that will return all values in column C that are not present in both columns A and B?

A   B   C   D   E
1   6   5       
5   7   7       
7   8   11      
10  9   12      
12  10  16      
    15  18      
        19

In other words, I would like column D to give: 6, 8, 9, 15 and column E to give: 11, 16, 18, 19.

Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including the code they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for code are too broad and are likely to be [put on hold or closed](http://stackoverflow.com/help/closed-questions). Please read [How do I ask a good question?](http://superuser.com/help/how-to-ask). — DavidPostill, Sep 20 '15 at 21:02
This question is subject of http://meta.superuser.com/q/10752/468548 — User15, Sep 21 '15 at 14:24

nixda · Answer 1 · 2015-09-24T21:19:12.133

2

Column D: =IF(COUNTIF(A:A,B1)=0,B1,"") » Autofill down
Column E: =IF(COUNTIF(A:B,C1)=0,C1,"") » Autofill down

edited Sep 24 '15 at 21:19

answered Sep 22 '15 at 06:10

nixda

26,823
17
108
156

Good answer. But wondering why `D7` gets a 0. – User15 Sep 24 '15 at 14:03
@User15: Because, if a formula refers to an empty cell, it gets a value of `0` unless you [jump through hoops](http://superuser.com/q/515932/150988 "Display Blank when Referencing Blank Cell in Excel") to avoid it. That’s why my answer says `IF(AND(B1 <> "", …), …, "")`. – Scott - Слава Україні Sep 24 '15 at 17:04
1

@nixda: Your formula for Column `E` can be simplified to `=IF(COUNTIF(A:B,C1)=0,C1,"")`. And, if you change your formula for Column `D` to `=IF(COUNTIF($A:A,B1)=0,B1,"")`, then you can drag/fill that *to the right* and down. – Scott - Слава Україні Sep 24 '15 at 17:05
@Scott I've added it – nixda Sep 24 '15 at 21:19

score 1 · Answer 2 · answered Sep 22 '15 at 04:12

1

It’s hard to do what you’re asking for all at once. Things like this are best done using “helper columns” to hold intermediate results. Here’s one approach:

Set F1 to =MAX(IF(B:B <> "", ROW(B:B)+3, 0)), typing Ctrl+Shift+Enter after you type the formula.
Set F2 to =INDEX(F:F, F1, 1).
Leave F3 blank.
Set F4 to =F3 & IF(AND(B1 <> "", COUNTIF($A:A, B1) = 0), B1 & ", ", ""). Drag/fill down to cover all your A/B data plus three rows.
Select Column F and drag/fill over to Column G.
Enter =LEFT(F2, LEN(F2)-2) in D1 and drag/fill over to E1.

answered Sep 22 '15 at 04:12

Scott - Слава Україні

21,399
46
64
121

Nice idea. Though I believe the question was to get 1 value per cell as nixda’s answer and not everything combined into 1 cell. – User15 Sep 24 '15 at 14:24
1

In hindsight, you’re probably right. The question says, “I would like column D to give: 6, 8, 9, 15 and column E to give: 11, 16, 18, 19.”, and I thought that the OP meant “I would like column D to give: `6, 8, 9, 15` and column E to give: `11, 16, 18, 19`.”, but didn’t know how to do the formatting. Maybe the OP will come back and say what he/she really wants. (And, BTW, maybe “unclear what you're asking” was the right disposition for the question.) – Scott - Слава Україні Sep 24 '15 at 16:25
When something is explained so nice and clear, you can’t just say it’s `unclear what you’re asking`. You need to ask **your specific question of what you need clarified**. And in fact no one mentioned they have this question even 3 days after this post was closed and I asked what’s not clear. – User15 Sep 24 '15 at 17:51

Non-intersecting values in columns in Excel

2 Answers2