finding list of keywords in a cell

[keerthi]

hi,

i have a list of keywords which i have to search for in each of the cell.each cell contains a list of words.i want to obtain the cell that has the maximum no.of keywords in the list.


Thanks in advance

 

[vasim]

Welcome to the forum Keerthi, First concatenate your keywords cells with some big value in the next column then use find formula,


= FIND(("concatenated word",concatenated cell)-1)


Hope this helps.

 

[keerthi]

sorry i dint get u..

can u explain it with an example..


what exactly i need is


the list of keywords represents the set of functions in a build that caused exceptions..

so when i get another list of functions i have to check if its the same list of functions that are causing the exceptions...

 

[Nunes]

hi,


from what i understood you want to count the number of keywords that can be found in a list of words in a cell, right???


assuming your base list of keywords is in a range of cells (each keyword in a cell), name that range something, like KEYWORDLIST.


now if you want to count the number of keywords found in a cell, you can use the following formula (assuming you want to search keywords in cell A1):


{=SUM(--NOT(ISERROR(FIND( KEYWORDLIST , A1))))}


note the {brackets} as you need to evaluate this formula as a vector (ctrl shift return)


hope this helps...

 

[Nunes]

the above formula is case sensitive, if you want to make it case unsensitive, you just have to use upper or lower in both arguments, key word list and cell to search, like this:


{=SUM(--NOT(ISERROR(FIND( upper(KEYWORDLIST) , upper(A1)))))}

 

[keerthi]

hi Nunes,

u understood my requirement correctly. but the formula u have given is throwing error.

im new to using excel.could u please explain what the formula is doing...the syntax for find and sum in ur formula looks different from what i have seen in other sites..


thanks alot..

 

[Nunes]

hi there...


we can check it, yes:


{=SUM(--NOT(ISERROR(FIND( KEYWORDLIST , A1))))}


imagine your list of keyords to search for is {aaa,bbb,ccc,ddd,eee}. you define this list as KEYWORDLIST range in excel.


in cell A1 there is something like "aaa ccc fff" therefore you want a formula that returns 2, as you can find 2 searchable keywords in cell A1 ("aaa" and "ccc").


the FIND() function tries to find a string of text within a cell, returning the position within the cell where the string is found... if the string is not found, it returns a N/A error.


when we run FIND( KEYORDLIST , A1), we are running FIND ( {aaa,bbb,ccc,ddd,eee} , "aaa ccc fff") and, BECAUSE WE ARE RUNNING IT IN VECTOR FORMAT (i can explain it later), he tries to find each element in KEYWORDLIST and returns a result for each element, so it would return somethig like:


FIND( KEYORDLIST , A1) =

FIND ( {aaa,bbb,ccc,ddd,eee} , "aaa ccc fff")=

{1,#N/A,5,#N/A,#N/A}

1 is the position where "aaa" is in "aaa ccc fff" and 5 the same for "ccc"... the #N/A are errors because "bbb" "ddd" and "eee" are not found in A1.


we have now {1,#N/A,5,#N/A,#N/A}


running ISERROR over this vector will return, of course


ISERROR( {1,#N/A,5,#N/A,#N/A} ) =

{FALSE,TRUE,FALSE,TRUE,TRUE}


running NOT over this will invert the TRUEs and FALSEs


NOT( {FALSE,TRUE,FALSE,TRUE,TRUE} ) =

{TRUE,FALSE,TRUE,FALSE,FALSE}


using the -- trick turns TRUEs into 1s and FALSEs into 0s, so putting the -- will give you:


--( {TRUE,FALSE,TRUE,FALSE,FALSE} ) =

{1,0,1,0,0}


note you now have a vector with 1s in places of keywords found and 0s in keywords not found... summing this vector will give you the number of keywords found.


SUM( {1,0,1,0,0} ) = 2 (ok this last one was kind of obvious! :D )


----------------

VERY IMPORTANT:

----------------


DO NOT FORGET TO RUN THIS FORMULA IN VECTORIAL FORMAT (this simply means you write the formula like a normal one, but you dont press only Return to validate it, you press Ctrl-Shift-Return).

 

[keerthi]

thanks alot...