Data Classification Using Keywords with VBA

Hi All,

Ok, So I have a "Description" column that contains a long text description in each cell, and I have thousands of cells like this.
So what I'm trying to do is to classify each cell by main keywords.

B.W it should be possible to have a few keywords for each description.
-----

I have 20 keywords, and my main issues of what I did so far are:

• Writing over each time
• Making sure "ITM" is a word by itself and not part of a word.
• Reliability of classification - I thought by counting the amount of time each words repeats itself within a cell.

This Is what I wrote so far:

Sub textsearch() Dim c As Range, scount As Integer, stText As String For Each c In Range("AL:AL") For Each Word In Array("feeder", " ITM ", " IMP ", "word1", " word2", " petrol", "Paper", "Oil") If InStr(1, c.Value, Word, vbTextCompare) > 0 Then c.Offset(, -2).Value = Word stText = c.Value scount = (Len(stText) - Len(Replace(stText, Word, ""))) / Len(Word) c.Offset(, -1).Value = scount End If Next Word Next c End Sub

Kind Regards,

You need to upload a sample of what you are dealing with because your description is not clear. Even so, here are some hints that should help

•Writing over each time

' I have no idea what that means... need a better description

•Making sure "ITM" is a word by itself and not part of a word.

' To make sure "ITM" is a word then add spaces around it so that it appears as " ITM " and then you need to add spaces around the text from the cell so that it will capture words at the beginning and end of string

Please Login or Register  to view this content.

•Reliability of classification - I thought by counting the amount of time each words repeats itself within a cell.

Not clear again. Why would reliability be associated with the number of times a word appears or doesn't appear.

Another issue that is not clear is what happens if several key words appear in the same cell. Should one supercede the other? Should they all be reported?

abousetta

Hi abousetta

First I wish to thank you.

You right it wasn't clear enough, but unfortunately it's not an option for me to upload it as for now.
I'll try to be more clear:

This is a descriptions of malfunctions and I wish to classify them automatically (for exmaple: oil pump related problem will get classify as "Oil Pump" but within the description there might be other keywords as "engine", Screw Pump etc.)
and at the end I should be able to get a report telling me what are the "top issues" that repeating the most.

Writing over each other - refers to what you just wrote at the bottom: " Should one supersede the other? " I'm not sure, my dilemma is what will be the best action to insure the reliability of the classification.
Should I just classify the description to multiply classifications ? or should I run some sort of test that will select the best keyword to classify by.


Reliability of classification - It was a long shot, maybe if a keyword will repeat itself more times within a cell then it will be the best keyword to classify by.

Any suggestions will be helpful

Thanks

Hi,

OK, several differing models are running through my mind right now. Can you create a dummy workbook with some examples? We don't need any real life data, but need to know what you are after. For example, do you know a priori what the keywords you are looking for are or is it a blackbox approach? Do you care if there is multiple key words in the same cell or is it binary (yes/ no), etc.

The reason we need to know is because the solution could be simple or a complex vba approach.

abousetta

OK, try this (if I understand you correctly)...

In a standard module

Please Login or Register  to view this content.

In D2, put this formula:

Formula:

Please Login or Register  to view this content.

E2 is the cell that contains the description. More than one cell selected will give an error message.
A$2:A$21 is the range that contains the keywords being checked. Use absolute references so that it doesn't move as you drag down.

The UDF will return all the keywords separated by commas (if N = 0) or the number of keywords found (if N = 1). If N is anything else than 0 or 1 then it will give you an error.

Let me know if this works for you.

abousetta

Thanks for the feedback.

Good luck.

abousetta

Amended in post #6.

abousetta