How to identify type of entity using key word

[iamkennyj]

To prepare my data for uploading into my system I have to scrub it and do the following....

I have a table of 10,000 plus records. Each record has a name field and a business indicator field which is empty. Based on the name field, I have to determine if that record is a business record or a personal record. If I determine it is a business, I put a "B" in the business indicator field. To do that I have to look for key words, letters and or phrases in the name field. For example if the name field has INC, LLC, LTD, ASSN, Group, etc etc in it I can label it as a business record. My problem is I have 10,000 - 20,00 records and about 200 key words/letters/phrases to search for. If I filter each record for each key word it would take forever to identify the business records (which is the objective).

Is there a smarter, better and faster way to identify which records are business records?


Sample Table 1

Acct Date Amt Business Indicator Name Address City State
123456 03/10/14 500.25 John Deere Inc John's street Philadelphia PA
125698 02/12/14 235.69 John Doe 1 ABC Drive Trenton NJ


sample list of key words (THERE ARE ABOUT 200 OF THEM)

ABC CITY
ADP COMM
AUTO CTR
BANK CTY
BUR DCP
BUIL INC

Looking at my table I would put a "B" in the business indicator column because of the INC in the name field. How could I determine this for 10,000 - 20,000 records, capturing 200 key phrases?

thank you

 

[thisoldman]

A single-cell array formula. Use Ctrl+Shift+Enter, not just Enter, after typing the formula in the first cell. Then copy down.

=IF(COUNT(SEARCH(My_Keywords,D1)),"B","")
​

I assumed the Names column starts in D1. I suggest using a defined name, "My_Keywords", to hold the list of keywords as an array constant, but you could also use a literal array constant. This is a condensed version of what your literal array constant would look like:

{"ABC";"AUTO";"BANK";"CITY";"COMM";"CTR";"CTY";"DCP";"INC";"LTD"}
​

 

[iamkennyj]

Thanks thisoldman, but I think I am missing something. While I think I understand the if statement, when I use it, it only gives me the blank space argument and no "B" when I know some of the records have the keywords I am lookng for

 

[thisoldman]

Okay. The formula will not work with a cell range, say $A$1:$A$12, for "My_Keywords". It only works with an array of constants or a defined name array of constants.

Here's instructions to duplicate what I did. Not the most efficient, but it works. I'm being explicit to prevent whatever went wrong going wrong again.

Create the named array
Enter a subset of the keywords in cells A1:A12 (see the list below). In B1, enter the formula

=A1:A12.
​

With B1 selected, click into the formula bar, select or highlight the entire formula, and then press "F9". The contents of the formula bar will change to:

={"ABC";"ADP";"AUTO";"BANK";"BUR";"BUIL";"CITY";"COMM";"CTR";"CTY";"DCP";"INC"}
​

Select the entire formula bar contents, including the equals sign and press Ctrl+c to copy the evaluated array to the clipboard. You can press Enter or Esc or click the X or the check mark to exit edit mode now that the literal array is copied.

From the ribbon, select Formulas > Define Name. In the "New Name" dialog, in the "Name:" box, type in "My_Keywords". In the "Refers to:" box, highlight the entire contents and press Ctrl+v to paste in the clipboard contents, the array formula. Note the formula begins with an equals sign, '=', and that the array is surrounded by curly braces, '{' and '}'.



Click "OK". You now have a named array of constants that exists in memory and isn't tied to a range of cells. You can delete the list in column A and the formula in B1.

The array formula
The test list is in D1:D5, see below. With only cell E1 selected, type the formula

=IF(COUNT(SEARCH(My_Keywords,D1)),"B","")​

Don't press "Enter" after typing the formula. Instead, press "Ctrl+Shift+Enter". If done correctly, Excel will place curly braces around the entire formula: one before the equals sign and one after the last parenthesis. Do not type the curly braces yourself.

If you did press "Enter", simply press "F2" to enter edit mode and then press "Ctrl+Shift+Enter".

Copy the formula down column E.

	D	E
1	JOHN DEERE INC.	B
2	John Doe
3	Johnstown Bank, N.A.	B
4	JON ADAMS
5	Jon's Auto Repair	B

<tbody>
</tbody>

Cell Formula E1 {=IF(COUNT(SEARCH(My_Keywords,D1)),"B","")} <tbody> </tbody>

<tbody>
</tbody>

 

[iamkennyj]

Thank you thisoldman for being explicit. It worked. I appreciate your assistance