identify multiple catagories with multiple key words

[Tinasloan]

I need to catagorize apx 5000 lines in to catagories 19 in all such as abrasives, tools, plumbing. by finding 650 key words in the description line of the sheet is there a function that will do this?

 

[Matty]

Welcome to the Board!

Let's assume your list of descriptions reside in Column A (starting in A2) and your categories resides in D2:D10. Now try this in Column B and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(D$2:D$10,A2),D$2:D$10)

Does this do as expected?

Matty

 

[Matty]

Reading this again, I may have overlooked something here.

Do you have a table already built that links the 650 key words to the 19 categories?

Matty

 

[Aladin Akyurek]

I need to catagorize apx 5000 lines in to catagories 19 in all such as abrasives, tools, plumbing. by finding 650 key words in the description line of the sheet is there a function that will do this?

You could start with the following set up...

Create a range housing 650 keywords and corresponding categories, say, A1:B650. Name A1:A650 KeyWords and B1:B650 Categories.

Let Sheet2 from A2 on those "apx 5000 lines"...

In B2 enter & copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(KeyWords,A2),Categories)

If the key words occur as stand-alone parts within the target strings, try...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&A2&" "),Categories)

 

[Matty]

A quickly mocked-up example:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 131px;"><col style="width: 63px;"><col style="width: 19px;"><col style="width: 79px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Descriptions</td><td>Category</td><td> </td><td>Key Words</td><td>Category</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Fine Grit Sand Paper</td><td>Abrasives</td><td> </td><td>Hammer</td><td>Tools</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Long Screw Driver</td><td>Tools</td><td> </td><td>Pipe</td><td>Plumbing</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Lump Hammer</td><td>Tools</td><td> </td><td>Sand Paper</td><td>Abrasives</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>Copper Pipe</td><td>Plumbing</td><td> </td><td>Screw Driver</td><td>Tools</td></tr></tbody></table>
B2, copied down:

=LOOKUP(9.99999999999999E+307,SEARCH(D$2:D$5,A2),E$2:E$5)

Matty
http://www.excel-jeanie-html.de/index.php?f=1

 

[Matty]

If the key words occur as stand-alone parts within the target strings, try...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&A2&" "),Categories)

Hi Aladin,

Could you give me an example where this particular set up would be needed over the other, please?

Thanks,

Matty

 

[Aladin Akyurek]

Hi Aladin,

Could you give me an example where this particular set up would be needed over the other, please?

Thanks,

Matty

Hi Matty

How about something like:

Key words: Snow, Juice

Strings to look at:

1) snowbird trail maps

2) snow news

3) juice please

4) bosch is the best juicer

I'm afreaid such might occur quite often.

 

[Matty]

Hi Matty

How about something like:

Key words: Snow, Juice

Strings to look at:

1) snowbird trail maps

2) snow news

3) juice please

4) bosch is the best juicer

I'm afreaid such might occur quite often.

Ah, got you now. Thanks for explaining. I'll get there eventually with this stuff...

Matty

 

[Tinasloan]

Yes I have the table built

 

[Tinasloan]

<TABLE style="WIDTH: 434pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=578><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=149>Adhesives, Sealants, Tape & Painting</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=149>Chemicals</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=149>Electrical </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=131>Foundry Supplies & Equipment</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Glue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">fuels</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Batteries</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Brick</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Adhesive</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Acid</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Battery </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Crucibles</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Threadlocking</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Caustic</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Enclosures</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Insulators</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Componant</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Grease</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">enclosure </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">crucible </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Caulk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Fluids</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Junction </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Insulators</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Guns</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Release </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">junction-box</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Insulator </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>coat</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">penetrating </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Metallic-boxes</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Ladles</TD></TR></TBODY></TABLE>

This is a partial of the table row 1 is the catagory listed down the column is the key word

This is a row from the actual spreadsheet I need to search column F that contains the part number description and return the rusult in column C
<TABLE style="WIDTH: 781pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1040><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" span=3 width=130><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 306pt; mso-width-source: userset; mso-width-alt: 14921" width=408><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc; WIDTH: 95pt; HEIGHT: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=28 width=127>72340</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=130>31162800</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=130>Hardware & Fasteners</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=130>Hardware</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=115>None</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 306pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=408>REMOVAL & TAPPING TOOL 1/4-28 1080K21</TD></TR></TBODY></TABLE>