Contains using advanced filter: order independent and more than two options

[cmv88]

I'm starting to use the advanced filters for sorting through my lists of academic journal articles. I like the ability to execute OR searches across different columns.

My current issue is trying to apply the advanced filter to find cells that contain, in any order, several strings. For example, I have a column titled "Authors". Perhaps I would like to filter the results to return all articles written by AuthorA AND AuthorB, regardless of whether or not they are first author, second author, etc.

Title	Author	...
Title1	AuthorA, AuthorB, AuthorC
Title2	AuthorC, AuthorB, AuthorA
Title3	AuthorC, AuthorA
Title4	AuthorA, AuthorB
Title5	AuthorA, AuthorC, AuthorB

<tbody>
</tbody>

If I do the following in the advanced filter:

Title	Author	...
	*AuthorA* *AuthorB*

<tbody>
</tbody>

This will return Title1, Title4, and Title5 because AuthorA and AuthorB are in the same order as entered in the advanced filter. However, I also want Title2 to show up as A and B are both authors, but it doesn't. I've tried other formulas, such as ="=*AuthorA* *AuthorB*", etc. and nothing seems to work.

I realize that in this case, I could run a custom text filter because I'm only looking for two criteria, and that would work well. However, what if I want to return cells that contain three authors? Furthermore, it would be nice to integrate this type of filtering with other features in the advanced filter.

Thanks in advance

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try this criteria range

Author​	Author​
*AuthorA*​	*AuthorB*​

Hope this helps

M.

 

[cmv88]

Thank you for the suggestion. This appears to work when I enter it as ="=*AuthorA*" and ="=*AuthorB*". It would be nice if it were possible to do it all in one column to maintain the mirror image of the table header and the advanced filter above it, but I understand this may not be achievable.

 

[Marcelo Branco]

Thank you for the suggestion. This appears to work when I enter it as ="=*AuthorA*" and ="=*AuthorB*". It would be nice if it were possible to do it all in one column to maintain the mirror image of the table header and the advanced filter above it, but I understand this may not be achievable.

It may be possible using a formula as a criterion, but we need more details to try to create such a solution. A small sample of the data and the criteria would be useful.
For testing purposes, a small sample of the data and the criteria along with the expected results would be useful.

M.