Adding Keywords

Topic

New Reply

Okay…

I have a database which tracks calls. Each row designates one call received by the representative.

Each call must be filtered into 5 seperate categories. I’m thinking I can give the rep the option to select a category, then I had another idea:

How would I setup keywords to ‘read’ the cell where the call notes are stored, searching for specific keywords, and if found, designate the proper call type? For example, one of our call types is Billing Question, If the word “billing” or “bill” or “bill” and “submit” or “bill” and “request” is in the body of the cell containing the notes, I want another cell, call it (AF,1) to respond by returning ‘TRUE’ Any Ideas?

thanks!!!

Drk.

Reply | Quote

Replies

Just a little something to start things.

If Cell A1 contains text, the following formula will return TRUE if that text contains “bill”, otherwise FALSE

IF(FIND(“BILL”,UPPER(A1),1),TRUE,FALSE)

How many keywords would you envisage per category ? If you have ideas to include quiet a few the above method might become cumbersome.

Hope that is some help

Andrew C

Reply | Quote

It’s looking like 10 keywords or more.. but i’ve got to build in ‘rules’ of sorts.. For example, if the keyword entered also applies to another one of the five types, it needs to be somehow flagged.

drk.

Reply | Quote

That last formula is better replaced by

=IF(ISERROR(SEARCH(D1,A1)),FALSE,TRUE)

SEARCH is not case sensitive, so is probably better than FIND(). Also the previous formula returned an error rather than FALSE if the text was not found.

To develop the idea any further I think we would need to know the
Rules you plan to apply.

I think your original idea of having the rep select the category would be better. It could be implemented with a simple Data Validation with Dropdown list.

However, It is up to you

Andrew C

Reply | Quote

The rules are, if criteria match any other category, then say, the field should say “Tech Review” as I would have to review the notes and make my own determination, as we are currently doing.

In addition to the ‘automatic’ system, we’ll have the rep selection system. In this case, it’s a userform, upon which they click the appropriate call type. The end result will be two sets of statistics, system percieved, and representative percieved, giving me, as the one who creates the reports, the ability to see at a glance flaws or mistakes in call-type assignment.

Hope that little novel makes sense!

Drk.

Reply | Quote

Drk,

Why don’t you use Data Validation List
You could put all the catagories in the data validation list so the rep only has to select the correct catagory from the puldown list

Reply | Quote

Well, from a reps perspective, it would take too much time. On a userform, they have 5 options right there, they just click on the appropriate checkbox, and move on with life. As they become more accustomed to where those checkboxes are, they don’t even have to think about it anymore. Data validation requires that they read the contents of the pulldown validation box. In the interest of saving time and increasing productivity, I prefer the checkbox method…

Thanks!

Drk.

Reply | Quote

OK, so if a checkbox suits, are you still interested in parsing an ad hoc note for the same information ?. It seems a little hit and miss, especially if the same keyword can have a meaning in different categories.

The checkbox approach seems best, and as it seems you are going to “audit” the report your self, you could make a judgemnet as to wheter the call type as checked matches the rep’s notes, which i would imagine you would have to do anyway. Andrew C.

Reply | Quote

That’s a good point… it’s beginning to sound like a little ‘good’ training on my part to educate the reps on call categorization would be the best solution here, and given that I will be ‘auditing’ the results, I can give corrective guidelines in the future, if at months end, the rep’s data is chaotic. Thanks alot Gents!

Drk.

Reply | Quote