Search text and categorise each description

[jonnyrobert]

Hi, had a good look but could not find an exact answer...

I am looking to categorise each text description into 1 of say 10 categories. I need to assign multiple words to equal each particular category, if 1 or more of those words occur then the category that they correspond to will be displayed in the final column.

I have found several posts relating to text search resulting in true/false result but how do I get a result where it shows which category the description falls into?

Key example:

Text Category

spreader	spreader
twin	spreader
frame	spreader
control	control
panel	control
power	control
gantry	gantry
(A)	Assist
Assist	Assist

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

Thanks!!!

Raw data comes in as below

Work Order

Description

Haslog

Engineer

Location

Asset

Status

Reported Date

Priority

Owner Group

Parent WO

Out of Service

Known Cost

2364905

QC17 TTY LOST spreader

Y

8952

TTYQC17

J3900

CLOSE

30/11/14 05:01

PFLFSQUD

N

8.71

2364210

QC17 TTY NO UNDERCAB FLOODLIGHTS spreader

Y

7183

TTYQC17

J3900

CLOSE

30/11/14 02:26

PFLFSQUD

N

279.37

2364101

Liebherr Remedials - PLC Mounts broken (A)

N

8972

TTYQC17

J3900

CLOSE

29/11/14 10:34

PFLFSQUD

N

0

2364100

Liebherr Remedials - Hoist Drive Fans U/S ~ Replace (A)

N

8972

TTYQC17

J3900

CLOSE

29/11/14 10:33

PFLFSQUD

N

0

2364099

Liebherr Remedials - Hoist Drive Fans U/S ~ Replace gantry

Y

8972

TTYQC17

J3900

CLOSE

29/11/14 10:32

PFLFSQUD

N

0

2364098

Liebherr Remedials - PLC Mounts broken

Y

7979

TTYQC17

J3900

CLOSE

29/11/14 10:31

PFLFSQUD

N

10.32

<COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10642" width=291><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY>
</TBODY>

 

[gaz_chops]

So in your example the results would be

spreader
spreader
Assist
Assist
gantry
NA

 

[jonnyrobert]

So in your example the results would be

spreader
spreader
Assist
Assist
gantry
NA

Yes exactly what I'm after

 

[Aladin Akyurek]

Let A1:B10 of a sheet called Admin house Text to Category associations. Let's name A2:A10 as KeyWords and B2:B10 as Categories.

Let A:M of Sheet1 house the data you want to categorize.

In N2 of Sheet1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$B2&" "),Categories)

 

[jonnyrobert]

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$B2&" ")),Categories)

I added the bracket highlighted red for excel to accept the formula....

Im sure its close but my results just return #Name?

How does the formula know to look in Admin?
Does this need to be entered as an array (crl+shift+enter)?
Im interested in this lookup vector 9.99999999999999E+307

Thanks for all your help, its valuable and Im learning from it

 

[Aladin Akyurek]

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$B2&" ")),Categories)

I added the bracket highlighted red for excel to accept the formula....

Im sure its close but my results just return #Name?

How does the formula know to look in Admin?
Does this need to be entered as an array (crl+shift+enter)?
Im interested in this lookup vector 9.99999999999999E+307

Thanks for all your help, its valuable and Im learning from it

Re-cap...

1) Let A1:B10 of a sheet called Admin house Text to Category associations. Let's name A2:A10 as KeyWords and B2:B10 as Categories.

2) Let A:M of Sheet1 house the data you want to categorize.

3) In N2 of Sheet1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$B2&" "),Categories),"NA")

That is, I initially forgat to add the result argement for IFERROR. You will get a #NAME? error if you do not the instruction (1).

See the following links for explanations:
9.9999999 .... ?
vlookup with multiple matches - which match is returned
Is there a case sensitive VLOOKUP?

 

[jonnyrobert]

Thanks for your prompt reply, I really appreciate your help.. Does it know to look in Admin?

With the amended formula I now get an NA result for N2 down.

Sheet 1 and sheet 2 renamed Admin:

Work Order

Description

Haslog

Engineer

Location

Asset

Status

Reported Date

Priority

Owner Group

Parent WO

Out of Service

Known Cost

Category

2364905

QC17 TTY LOST gantry spreader

Y

8952

TTYQC17

J3900

CLOSE

30/11/14 05:01

PFLFSQUD

N

8.71

NA

2364210

QC17 TTY NO UNDERCAB FLOODLIGHTS spreader

Y

7183

TTYQC17

J3900

CLOSE

30/11/14 02:26

PFLFSQUD

N

279.37

NA

2364101

Liebherr Remedials - PLC Mounts broken (A)

N

8972

TTYQC17

J3900

CLOSE

29/11/14 10:34

PFLFSQUD

N

0

NA

2364100

Liebherr Remedials - Hoist Drive Fans U/S ~ Replace (A)

N

8972

TTYQC17

J3900

CLOSE

29/11/14 10:33

PFLFSQUD

N

0

NA

2364099

Liebherr Remedials - Hoist Drive Fans U/S ~ Replace gantry

Y

8972

TTYQC17

J3900

CLOSE

29/11/14 10:32

PFLFSQUD

N

0

NA

KeyWords Categories spreader spreader frame spreader flipper spreader flippers spreader hoist hoist lower hoist gantry gantry assist assist (A) assist <COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY> </TBODY>

<COLGROUP><COL style="WIDTH: 48pt" span=14 width=64><TBODY>
</TBODY>

 

[Aladin Akyurek]

Thanks for your prompt reply, I really appreciate your help.. Does it know to look in Admin?

With the amended formula I now get an NA result for N2 down.

[...]

See the workbook that implements the proposal: https://dl.dropboxusercontent.com/u/65698317/jonnyrobert lookup+search.xlsx