3

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.

Improve this question
3
  • 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
    – Kevin Panko
    Sep 22, 2015 at 2:23

2 Answers 2

Reset to default
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

Improve this answer
4
  • 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 <> "", …), …, "").
    – Scott - Слава Україні
    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.
    – Scott - Слава Україні
    Sep 24, 2015 at 17:05
  • @Scott I've added it
    – nixda
    Sep 24, 2015 at 21:19
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.
Improve this answer
3
  • 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.)
    – Scott - Слава Україні
    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.