Searching a table of data using an array and returning the array's header?!?

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
 
C

Claus Busch

Hi,

Am Wed, 30 Oct 2013 18:25:48 -0700 (PDT) schrieb
(e-mail address removed):
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

your key words in Sheet2. Then try in Sheet1 E2:

=IF(COUNT(SEARCH(Sheet2!$B$2:$E$2,B2))>0,Sheet2!$A$2,"")
and array-enter the formula with CTRL+Shift+Enter

Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "StrComp"


Regards
Claus B.
 
A

Albert

Hi Claus Bush
Claus Busch said:
Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "StrComp"
Regards
Claus B.

I read the Microsoft.public.excel forum with the Windows Mail reader, when
I click on your Skydrive link it opens the page Skydrive and prompt me to
enter my Live ID and password, then it opens my hotmail account, but does
not open your skydrive.

To open your link, I must send the message to my Hotmail address, when the
message is in the Hotmail receive box a click on the link will open it.

I am being told that maybe you are using the option that the receiver must
be connected to a Microsoft account, could this be the case ?

Thanks, I am trying to find how it works and I find most of your reply are
of interest.

Tks
 
C

Claus Busch

Hi Albert,

Am Sun, 10 Nov 2013 09:18:12 -0500 schrieb Albert:
I read the Microsoft.public.excel forum with the Windows Mail reader, when
I click on your Skydrive link it opens the page Skydrive and prompt me to
enter my Live ID and password, then it opens my hotmail account, but does
not open your skydrive.

you don't have to be connected to SkyDrive. With a double click you can
open the file into the WebApp. Or you can right click and download it to
your PC.


Regards
Claus B.
 
A

Albert

Hi

Claus Busch said:
Hi Albert,

Am Sun, 10 Nov 2013 09:18:12 -0500 schrieb Albert:


you don't have to be connected to SkyDrive. With a double click you can
open the file into the WebApp. Or you can right click and download it to
your PC.
Regards
Claus B.

Thanks, but still does not work on my two PCs,
Left or right click leads to the same result, it displays the Skydrive
page and it opens my hotmail account, my mail and my Skydrive, it does not
open your link.
I still searching for the problem.
Tks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top