# Non-intersecting values in columns in Excel

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. Please read How do I ask a good question?. Sep 20, 2015 at 21:02
• This question is subject of meta.superuser.com/q/10752/468548 Sep 21, 2015 at 14:24

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

• Good answer. But wondering why `D7` gets a 0. Sep 24, 2015 at 14:03
• @User15: Because, if a formula refers to an empty cell, it gets a value of `0` unless you jump through hoops to avoid it.  That’s why my answer says `IF(AND(B1 <> "", …), …, "")`. Sep 24, 2015 at 17:04
• @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. Sep 24, 2015 at 17:05
• @Scott I've added it Sep 24, 2015 at 21:19

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`.
• 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. Sep 24, 2015 at 14:24
• 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.) Sep 24, 2015 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. Sep 24, 2015 at 17:51