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      

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

  • 2
    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?.
    – DavidPostill
    Sep 20, 2015 at 21:02
  • 1
    This question is subject of meta.superuser.com/q/10752/468548
    – User15
    Sep 21, 2015 at 14:24
  • @nixda You may go ahead and write your answer now Sep 22, 2015 at 2:23

2 Answers 2

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

enter image description here

  • Good answer. But wondering why D7 gets a 0.
    – User15
    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
  • 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. Sep 24, 2015 at 17:05
  • @Scott I've added it
    – nixda
    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.
    – User15
    Sep 24, 2015 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.) 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.
    – User15
    Sep 24, 2015 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.