Excel advanced filter failed

Question

Hi, All,

I am trying to use Excel (version 16.79.2 for Mac OS) advanced filter tool to filter items in a table (sheet name: List) based on certain criteria in another sheet (sheet name: criteria). I tried "Data --> advanced --> advanced filter --> filter in place" and also chose the List and criteria range. While certain items in the table meet the conditions of the criteria, the filter failed and only the header row was in the output.

I appreciate it if you guys could give me some input to solve this problem. The screenshot to illustrate my problem and the excel file is in the link below.

https://drive.google.com/drive/folders/1Nt037QN89GRqAPtbuIE1z_od5HLVFpwA?usp=drive_link

Thanks,

Xiao

Answer 1

It seems your data in column A in criteria sheet has unknown character in it.

Try this formula to remove the unknown characters, paste the formula result to column A, then advanced filter will work.

=CLEAN(A2)

Here is the result:

Answer 2

All the gene symbols on the Criteria sheet have two tab characters in front of them (if you test them with LEN you'll see that they are 2 longer than they appear). You can use another column with the CLEAN function to remove the tabs, then copy and paste as values back over the original data.

Answer 3

Hi, Snow and Rory,

Thank you very much! Your suggestions work! I have a related question, in this case, is there a way to do the "reverse filter"? For example, if I want to output the items in the list that do NOT meet the filter criteria, is there a way to do this?

Xiao

Answer 4

I have no idea to get it using advanced filter.

As workaround, based on the first result.

Add a help column. Copy any value.

Ctrl+G > choose visible cell only. paste the value.

For those columns without number in it is the reverse selection you want.

Answer 5

I have no idea to get it using advanced filter.

As workaround, based on the first result.

Add a help column. Copy any value.

Ctrl+G > choose visible cell only. paste the value.

For those columns without number in it is the reverse selection you want.

Thank you although the question is still unanswered. There seems to be no way to do this by mouse click, I tried to click "unique records only" under the advanced filter panel but this does not serve my purpose. I aimed for a similar output (filter in place) of a list as I used the advanced filter but the items in the output are those that do not meet the criteria. I hope one day Excel will make this feasible.