Excel 2007 : Filtering out via a list

[reidlos65]

I am working with excel alot on my new job, I am looking to see if there are any good methods to take a list of data and find it in a larger sheet.

Detailed example:

I have a spread sheet that has 100s of rows of data in several columns
One column is a list of Product IDs, recently i was giving a list of IDs that had to have another set of data updated (a second set of IDs) This is all from random ranges, I want to be able to take a list, put it into a sheet, and use that to search/filter only those. This seems rather simple but Search only lets you find 1 cell at a time, and the text filters is restrictive as well, The advanced filter I thought would work but I dont think I did it right because it wasn't displaying anything

Can anyone give a more detailed adv filter walkthru or another method?

Thanks

[NBVC]

An easy way would be to add a helper column that returns TRUE or FALSE based on if product ID is found in your subset.

eg

Assuming your product Id column is column A and your the IDs you want to filter is listed in Sheet2 A1:A10,

Then in a new column of the data sheet enter formula like:

=ISNUMBER(MATCH(A2,Sheet2!$A$1:$A$10,0))

copied down.

Now filter for TRUE on this column.


To use Advanced Filter, make sure the title on Sheet2 listing the Product IDs is the same as the one in the Database... then while in the database select the table and invoke Advanced Filter, make sure your selected table is in the top field, the criteria field you need to point to the sheet2 table with header...