Advanced Filter By Cell Contents

[EE]

Hi

I have a column that contains different terms seperated by Comma.
Similar to "Apple, Ball, Cat,Dog, and so on".

I have upto 15 unique values that could be there and different rows
have different number of values. Some rows have no values, some can
have 3 "Apple,Cat,Egg" and some can have 12.

Now I need to find out how many records have a combination of values.
I could have used filter if I I needed two but there may be times I
want to find the number of records that have 9 of the values. (For
example iof these 9 are present in a row that has 12 values, even that
row should be displayed. I am NOT looking for EXACT match.)

Can this be solved using some worksheet functions?

Thanks in Advance

Best
Prasad

 

[EE]

Hi

I have a column that contains different terms seperated by Comma.
Similar to "Apple, Ball, Cat,Dog, and so on".

I have upto 15 unique values that could be there and different rows
have different number of values. Some rows have no values, some can
have 3 "Apple,Cat,Egg" and some can have 12.

Now I need to find out how many records have a combination of values.
I could have used filter if I I needed two but there may be times I
want to find the number of records that have 9 of the values. (For
example iof these 9 are present in a row that has 12 values, even that
row should be displayed. I am NOT looking for EXACT match.)

Can this be solved using some worksheet functions?

Thanks in Advance

Best
Prasad

Hi

I found a workaround (or maybe its the solution). What I did was this.

My Requirements was to have a multiple "AND" conditions. SO I added 15
columns to my data with the first row defined as ="=*Apple*",
="=*Ball*" and so on. The reason for this was that I am not looking
for an exact match of Apple but I am looking for the word Apple in a
string that contains other words too.

Then I created a formula under the column in the next row =MAX
(COUNTIF($CK2,DA$1)).......CK2 is the cell I am searching for the text
that is in DA1. I replicated this formula for 15 columns and 5000+
rows.

Now using Auto filter I can serach for the value of "1" or "0" in the
15 columns to get what I need.

DO let me know if there is a more elgant solution.

Thanks again and hope this helps someone.

Best
Prasas