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

[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

 

[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.

 

[Albert]

Hi Claus Bush

Hi,

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

 

[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.

 

[Albert]

Hi

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

 

[Ron Rosenfeld]

I am attempting to search a very large data dump from a database for certain keywords that would be grouped together.

This can be done fairly easily with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=Category(B2)

in some cell e.g: If column B is "Description", and Column E "Category", you could put the formula in E2, and fill down as needed

================================
Option Explicit
Function Category(s As String) As String
Dim vCat As Variant
Dim i As Long, j As Long

vCat = Range("Search1")

For i = 1 To UBound(vCat, 1)
For j = 1 To UBound(vCat, 2)
If InStr(1, s, vCat(i, j), vbTextCompare) > 0 Then
Category = vCat(i, 1)
Exit Function
End If
Next j
Next i
End Function
==============================