CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formula
viv227295
viv227295

Reputation: 397

How to filter one list of items from another list of items?

I have a huge list of items in Column A (1,000 items) and a smaller list of items in Column B (510 items).

I want to put a formula in Column C to show only the Column A items not in Column B.

How to achieve this through a formula, preferably a FILTER formula?

Upvotes: 7

Views: 26761

Answers (3)

player0
player0

Reputation: 1

ARE formulae:

=FILTER(A1:A, MATCH(A1:A, B1:B, 0))

=FILTER(A1:A, COUNTIF(B1:B, A1:A))

ARE NOT formulae:

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

=FILTER(A1:A, NOT(COUNTIF(B1:B, A1:A)))

in your case:

=FILTER(A1:A; ISNA(MATCH(A:A; B:B; )))

enter image description here

if you face a mismatch of ranges see: https://stackoverflow.com/a/54795616/5632629

Upvotes: 1

Tnargwoxow
Tnargwoxow

Reputation: 1

Alternative method is simply =

FILTER(A1:A,if(COUNTIF(B1:B,A1:A),0,1))

It's much more efficient. It uses countif to get a 0 or a 1 as an array if the values in B are in A, then it reverses the 0 and 1 to get the values that are missing instead of only the values that are in there. It then filters based on that. Columns look like this

A   B
1   2
2   5
3
4
5

Upvotes: 0

Andy
Andy

Reputation: 181

  1. Select the list in column A
  2. Right-Click and select Name a Range...
  3. Enter "ColumnToSearch"
  4. Click cell C1
  5. Enter this formula: =MATCH(B1,ColumnToSearch,0)
  6. Drag the formula down for all items in B

If the formula fails to find a match, it will be marked "#N/A", otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

If you'd like to return the unfound value and return empty string for found values

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)

Upvotes: 12

Related Questions