45

I have two columns A and B in a google spreadsheet.

I want to find all the numbers that are in column A but not in B. How do I do that?

B could have the same numbers from column A.

So if column A has numbers: 1, 2, 3 and B has numbers 3, 4,5

I want to get all the numbers that are in A but not in B: 1, 2

How do I do that using google spread sheet?

3 Answers3

69

Use MATCH to determine whether each row in column A appears in column B, then filter column A to only the rows for which MATCH returned #N/A (i.e., that row's value in column A could not be found in column B):

=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)))

If A contains duplicates and you want to reduce the result sequence to unique values, just wrap the whole thing in UNIQUE:

=UNIQUE(FILTER(A:A, ISNA(MATCH(A:A, B:B, 0))))
Kate
  • 1,263
  • 10
  • 13
  • This may work back then, now I need to use ISNA instead of ISERROR. – Geoffrey Zheng May 06 '16 at 19:16
  • [`ISNA`](https://support.google.com/docs/answer/3093293) is more specific, and therefore probably a better choice (will update), but [`ISERROR`](https://support.google.com/docs/answer/3093349) does still work in Google Sheets. [`ISERR`](https://support.google.com/docs/answer/3093348) will not work, however, as it returns `FALSE` on `#N/A` -- perhaps that's what you tried? – Kate Jun 06 '16 at 14:59
  • Works perfectly! Thanks :) – Mohit Singh Jan 20 '17 at 09:02
  • 1
    @Kate great! This works for the whole rows of `A` and `B`, but how put the results in a new column `C`? – loretoparisi Jul 05 '19 at 06:37
  • I tried the solution given in this thread with no luck. Then I came across an alternative with a minor `{}` tweak and that worked. For example: ```=UNIQUE(FILTER({A:A}, ISNA(MATCH(A:A, B:B, 0))))``` Here's a reference to the solution I picked this from: https://support.google.com/docs/thread/10882571/results-that-match-and-do-not-match?hl=en – Ekanem Eno Jan 10 '22 at 11:43
11

Based on Kate aswer I have been able to negate not only one column, but several.

Kate solution was as follows:

=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)))

Where "B:B" is defining that what is going to be returned is A:A less B:B.

But if you want to return A:A, lees B:B, less C:C, less D:D, etc? Just insert B:B, C:C and D:D inside {}, then:

=FILTER(A:A; ISNA(MATCH(A:A; {B:B;C:C;D:D}; 0)))

I hope this may help others like me. I was seeking for a solution that would not bring what's present in some set of columns.

Thanks!

Gabz
  • 391
  • 5
  • 11
2

In column C, use vlookup from A against B. For example

C1 = vlookup(A1,B:B,1)
C2 = vlookup(A2,B:B,1)
...

If B does not contain A, then it shows #N/A

Fabricator
  • 12,722
  • 2
  • 27
  • 40