Filtering column on multiple criteria - looking for most efficient way

I have a column with that contains initials of authors. What I need to do is filter out some authors, leaving me with only the authors whose data I want to look at. Sometimes there's only one author in the cell, sometimes there's multiple (with a comma delimiter between them). What is the most efficient way to filter down to just cells where the authors I'm interested in are contained?

I thought of using a contains filter on the column - Contains([Authors],"XYZ"), but I have about 50 authors and I would prefer not to have to put in 50 of these. Is there a more efficient way to do this? The list of authors I have that I want to filter down to is currently just free text, but I could easily add them to another excel sheet if needed.

Preparation

Accepted answers

LordNeilLord

Hey @InfoManager

I'm thinking Find and Replace may be a good way to achieve this. If you create a list of the initials you want to search and then use F&R to search your dataset.

I have attached an example

F&R.PNG

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

F&R.yxmd

All comments

LordNeilLord

Hey @InfoManager

I'm thinking Find and Replace may be a good way to achieve this. If you create a list of the initials you want to search and then use F&R to search your dataset.

I have attached an example

F&R.PNG

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

F&R.yxmd

Garrett

I'd suggest using the Text to Columns tool to split out the authors column to individual rows using comma as a delimiter. Then with one row per author use a Join tool (pointing to your list of target authors as the other input) to filter out just the authors you want. Assuming you assigned a unique row ID (i.e. Record ID tool) before you parsed into rows you can now easily join back to the incoming rows where row ID matches row ID. See attached.

Big drawback of using the Join is that you'll find only exact matches, rather than simply "contains" type matches. If you need to use "contains", I'd recommend taking a looking at the Dynamic Formula tool (CReW Macros).

parse to rows.yxmd

InfoManager

Thank you! Very helpful.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335