Search code examples
excelexcel-formulaexcel-match

Filter a spreadsheet with a list of ID's

I have a large spreadsheet with a ton of information (10,000+ records). One of the columns is an ID column.

I also have a list of around 500 ID's that need to be excluded from this dataset.

Basically I want filter through the BIG spreadsheet and spit out a spreadsheet with everything excluding the list of ID's that must be excluded.

Example Data:

id name email
34 bob  [email protected]
59 amy  [email protected]
53 rog  [email protected]
98 jon  [email protected]

Filter Data (list of id's I want to filter out):

id
34
53

The result would look like:

id name email
59 amy  [email protected]    
98 jon  [email protected]

What would be the best way to handle this using Excel, Libre Office, or Google Docs?

Solution

  • Sice you don't mention where your 500 list is, assign it a named range (eg Dlist, of workbook scope in the same workbook) and apply a formula such as: 

     =MATCH(A2,Dlist,0)

    copied down then filter to select for everything other than #N/A. and copy what/where required.