Advanced filtering allows you to extract a subset of rows from a worksheet based on a list.
Open http://rous.mit.edu/~charliew/Biol2Bioinfo_Materials/ExampleDataPart.xlsx ExampleDataPart.xlsx]
Create a Criteria List
Criteria List should look like this
Gene Symbol Epcam Esco1
Paste it a few cells beneath the column being used to filter the data
- NOTE: Be sure this sub-list has the same column name as the list being filtered.
- Note activation is done using Data-->Filter-->AdvancedFilter
Select them appropriately and click OK
Only filtered rows will be displayed. Successful filtering is indicated by blue row numbers.
When searching with words (rather than numbers), the Advanced Filtering function will simply match by beginning-of-string identity. Thus, searching e.g. for 'MARCH1' with Advanced Filtering will find not only 'MARCH1' but also 'MARCH10' and 'MARCH11'!
SOLUTION: For both search items and to-be-searched items, use the Excel function "=concatenate(..." to create a new column by appending a delimiting character, e.g. '^'. In this example, the resulting string 'MARCH1^' will no longer match 'MARCH10^' and 'MARCH11^'.