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

In L2 on your criteria sheet enter:

=AND(BYROW(July_Sep_FDR_Up_intersect_1,LAMBDA(r,NOT(AND(r=List!C2:K2)))))

and leave L1 blank.

Now use the advanced filter on the List but specify L1:L2 on the Criteria sheet as your criteria range.