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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Title1
[/TD]
[TD]AuthorA, AuthorB, AuthorC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Title2[/TD]
[TD]AuthorC, AuthorB, AuthorA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Title3[/TD]
[TD]AuthorC, AuthorA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Title4[/TD]
[TD]AuthorA, AuthorB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Title5[/TD]
[TD]AuthorA, AuthorC, AuthorB[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If I do the following in the advanced filter:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*AuthorA* *AuthorB*[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="class: grid"]
[tr][td]

Author​

[/td][td]

Author​

[/td][/tr]

[tr][td]

*AuthorA*​

[/td][td]

*AuthorB*​

[/td][/tr]
[/table]

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.