B
bundybear069
Hi,
I am attempting to search a very large data dump from a database for certain keywords that would be grouped together.
For example, the database information is in a format such as below:
Date Description Fault Reason
1/1 A/C won't work power circuit failure
5/1 ac not cold condenser iced up
7/1 air conditioning too hot User Set temp
9/1 Phone broken network Congestion
15/1 handset broken user user
24/1 temp to cold user set temp
What i am trying to do is search the 'Description' column for key words that i have in a table (named as Search1) such as follows:
Category Possibly entered as
A/C A/C ac air conditioning temp
PABX Phone handset PABX telephone
I have used the following formula using ctrl/shift/enter to get it to work as an array
{=IF(COUNT(SEARCH(Search1,B2)),"match","no match")}
and this returns either "match" or "no match" which is almost there but what i really want to return is instead of "match" i want the corresponding value in the 'Category' column of the Search1 table.
For example, if it performs the search and finds "temp" in the cell, I would like the value "A/C" returned instead of "match".
Then if it performs the search and finds "handset" it would return PABX.
No matter what i try i have no idea how to get this final bit of functionality working.
Any help would be great.
Thanks
David
I am attempting to search a very large data dump from a database for certain keywords that would be grouped together.
For example, the database information is in a format such as below:
Date Description Fault Reason
1/1 A/C won't work power circuit failure
5/1 ac not cold condenser iced up
7/1 air conditioning too hot User Set temp
9/1 Phone broken network Congestion
15/1 handset broken user user
24/1 temp to cold user set temp
What i am trying to do is search the 'Description' column for key words that i have in a table (named as Search1) such as follows:
Category Possibly entered as
A/C A/C ac air conditioning temp
PABX Phone handset PABX telephone
I have used the following formula using ctrl/shift/enter to get it to work as an array
{=IF(COUNT(SEARCH(Search1,B2)),"match","no match")}
and this returns either "match" or "no match" which is almost there but what i really want to return is instead of "match" i want the corresponding value in the 'Category' column of the Search1 table.
For example, if it performs the search and finds "temp" in the cell, I would like the value "A/C" returned instead of "match".
Then if it performs the search and finds "handset" it would return PABX.
No matter what i try i have no idea how to get this final bit of functionality working.
Any help would be great.
Thanks
David