Filtering items on several keywords in different sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1993834%22%20slang%3D%22en-US%22%3EFiltering%20items%20on%20several%20keywords%20in%20different%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993834%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20working%20on%20a%20file%20that%20contains%20over%20100.000%20keywords%20that%20I%20need%20to%20group%20and%20filter.%20However%20I%20can't%20find%20a%20good%20way%20to%20do%20this.%20For%20example%20I%20want%20to%20filter%20on%20the%20word%20motorbike%2C%20however%20they%20can%20be%20spelled%20like%20motorbike%2C%20motor%20bike%2C%20moto%20bike%20and%20motorcycle.%20Is%20there%20a%20good%20way%20to%20select%20all%20these%20words%20into%20a%20new%20sheet%20so%20I%20can%20easily%20see%20what%20word%20is%20more%20important%20than%20others%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20is%20an%20example%20of%20how%20my%20real%20file%20is%20constructed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1993834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993905%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20items%20on%20several%20keywords%20in%20different%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903962%22%20target%3D%22_blank%22%3E%40Emiel52%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20familiar%20with%20the%20new%20FILTER%20function%3F%20You%20can%20use%20multiple%20criteria%20(in%20this%20case%2C%20multiple%20spellings)%20into%20the%20function%20and%20produce%20on%20another%20sheet%20an%20extract%20of%20the%20requested%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20YouTube%20video%20that%20explains%20the%20function.%20Or%20you%20could%20upload%20a%20copy%20of%20that%20sample%20sheet%20(an%20image%20is%20less%20helpful%2C%20frankly)%20and%20I%20or%20someone%20else%20can%20create%20the%20formula%20in%20that%20sample.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

 

I'm currently working on a file that contains over 100.000 keywords that I need to group and filter. However I can't find a good way to do this. For example I want to filter on the word motorbike, however they can be spelled like motorbike, motor bike, moto bike and motorcycle. Is there a good way to select all these words into a new sheet so I can easily see what word is more important than others?

 

The attached file is an example of how my real file is constructed.

2 Replies

@Emiel52 

 

Are you familiar with the new FILTER function? You can use multiple criteria (in this case, multiple spellings) into the function and produce on another sheet an extract of the requested data. 

 

Here's a YouTube video that explains the function. Or you could upload a copy of that sample sheet (an image is less helpful, frankly) and I or someone else can create the formula in that sample.

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Never tried it myself but you might find it useful (the description isn't crystal clear):
https://www.microsoft.com/en-us/download/details.aspx?id=15011