Search for multiple strings in a list (w/in 1 cell) w/ Advanced fi

[Maher]

Hi,
I'm using Advanced filter, to search for a different criteria and now I'm
stuck with this problem. One of my columns contains a list os secondary
languages:

Users Name Secondary Languages
Joe french, german, italian
Fred Japanese, french, arabic

Using advanced filter, if my criterion is *french*german , it yields Joe,
which is great, but if my criterion is *german*french it yields nothing.
Is there anything in excel like `*{german, french}` where it can find the
two strings regardless of their order?

 

[Jim Rech]

I don't know how it will work in practice but you might trying doing
something with two (or more) criteria cells. Like this for French and
German:

Lang Lang
*french* *german*


Yes, you can have field names more than once in your Criteria range.

--
Jim
| Hi,
| I'm using Advanced filter, to search for a different criteria and now I'm
| stuck with this problem. One of my columns contains a list os secondary
| languages:
|
| Users Name Secondary Languages
| Joe french, german, italian
| Fred Japanese, french, arabic
|
| Using advanced filter, if my criterion is *french*german , it yields Joe,
| which is great, but if my criterion is *german*french it yields nothing.
| Is there anything in excel like `*{german, french}` where it can find the
| two strings regardless of their order?

 

[Maher]

Thanks for the reply Jim,
Currently, I can search in 2 ways ,
I search for *german *french OR *french *german (2 rows of criteria)
or I search for french first and filter the results for german.
(By the way *german* *french* work exactly the same)

But what I wanted to do is to just search for (german, french), regardless
of the order, in one step. Especially if I search for more than 2 languages
and it will take more lines of criteria or more successive filtering. Just
imagine, for 3 strings I need 6 combination, i.e. 6 rows of criteria, or
three successive filtering. This can be annoying for regular users.

Also the problem, in first place, is that I don't know what languages the
users will input, so really I can't have separate column for each language.
And also, I have the same problem in different fields, not just the language.
Thanks

 

[Jim Rech]

With the design restraints and requirements you have I think the "solution"
would have to involve a macro driven interface and search. I'd envision a
userform with a checkbox for each language as a start...

 

[Art Farrell]

Hi,

I think what Jim gave you works OK for me. Here's a sample if the email
doesn't change everything:

Secondary Languages Secondary Languages Secondary Languages Lang.
*german *Japanese *arabic
*arabic

*english

*french

*german

*italian

*Japanese
Users Name Secondary Languages
Joe french, german, italian
Fred Japanese, french, arabic, german
Ben Japanese, french, arabic,italian
Tom german, french, arabic, Japanese
Bill english, german, french


This shows three Criteria blocks and the entry order of the languages
doesn't matter. I would suggest you list all your languages in a column to
the right so you can select what you need and paste it in. If one or two of
your entry blocks is blank it won't matter.

CHORDially,
Art Farrell

 

[Maher]

Thanks guys,
The multiple criteria columns worked for me. Yet, I still believe it would
have been much easier if excel had something like this: `*{string2, string3,
....}`
Anyway, I'm still a beginner and I very much appreciate your help.
Thanks again, Jim and Farrell.