Need Help with developing formula for searching and categorizing data to know the pattern.

[Malav Shelat]

Hello all,

I have a columnA containing certain data(String data type), 

-If that data contains a word (Root cause/RootCause) the search should start from the word Root cause/RootCause till the end of the String

 and categorize data to related keyword

-if that data does not contain that word (Root cause/RootCause) then it should search the whole data.

Certain Keywords will be like: ocx, activate, educate, configure, wanted to know, upgrade, Permissions, upload, NA or Others etc etc.

here is the example datasheet

somedata somedata somedata… and some more data: Chanel#Connection and application closed:yesRoot Cause:  name was selected instead of "ALL" under facility.	(As there are no keywords matching we can define this as OTHERS)
.--> Educate to enter the captcha properly .--> stll it was not changing the status.--> Ran ecw dependencies and cwreg.bat file.--> gave full permissions to eclinical folder.--> asked her to login and issue still persists.--> Issue is fixed.--> .Root Cause: Register .OCx and .Dll on computer.	(This should fall under ocx keyword category, as data contains word .OCx)
This is an automated message.This case is being marked as closed	(As there are no keywords matching we can define this as OTHERS)
Did not take connection: Wanted to know about upgrade	(This should fall under upgrade keyword category, as data contains word upgrade)
somedata somedata somedata ...Root Cause: did not have the permissions	(This should fall under Permission key word category, as data contains word permission)
Ereply.Problem Resolution: Scheduled after hours to upload  as per request.Root Cause: Request to upload  file.	(This should fall under UPLOAD keyword category)
File -> setting -> My setting ->  Configure Assinged Favourites.- Add the user on the right side.- close the case my setting	(As there are no keywords matching we can define this as OTHERS)
some data somedata somedata ..... there is some more data: NA	(This should fall under NA keyword category)
somedata somedata somedata: install the software on one pc.	(This should fall under Install keyword category)
to close the ticket.Root Cause: Wanted to Inactivate users	(This should fall under ACTIVATE key word category, as data contains word Inactivate)

- So for example, if Column A2 contains "Register .OCx and .Dll on computer." string and also has the Root Cause mentioned in it, the search should start for root cause till the end of data for that cell and it should be categorized into keyword OCX as the cell contains that word in this statement "Register .OCx and .Dll on computer."

-similarly it should act for different keywords and categorize them accordingly, and where a cell does not contain Root cause word then in such case the whole sentence would be searched and it should categorize data accordingly. (like an else statement)

kindly helpout, i am having hard time figuring this out.

Thank you

Malav Shelat

[#2 · Accepted solution]

Hi Malav,

please find attached my attempt to resolve this. I have created a lookup table (tblLookup) with lookup words and respective categories. Then, in the main data table I did the following:

1. Found "Root cause" starting position. If this word does not come up in the original string, the formula returns 1: =IFERROR(FIND("Root Cause",[@[Original Text String]]),1)

2. Trimmed the original text string to start with the words "Root Clause": RIGHT([@[Original Text String]],LEN([@[Original Text String]])-[@[Root Cause Start]]+1)

3. Used the following formula to find the resulting category: =IFERROR(LOOKUP(PI(),1/COUNTIF([@[String to Search]],"*"&tblLookup[Lookup Words]&"*"),tblLookup[Category]),"Other")

Hope this helps

Yury

[#3]

Thank you very much for your effort Yury, i appreciate the help. Actually i had developed an If-else scenario for the same but this is what i actually wanted. 

Thank you again ! :)