Classifying by description

**Kupaaq** · 02-04-2011, 05:15 PM

I have a spreadsheet with several thousand brief equipment descriptions. I need to put them into classes such as "pump," "motor," and "power regulation." I started doing this successfully with nested IF(ISNUMBER(SEARCH("KEYWORD",CELL)),"CLASS",) statements, but I have too many classifications (error: too many arguments), so I can only run 5 or 6 at a time, and it's just not working well.
So I've included a sample of the keywords and corresponding classes below. It seems there must be a function to check a specified cell (containing an equipment description) for any of the text on the left, and then insert the corresponding class in the right column. Can someone tell me how to do this?

PUMP______________________PUMP
MOTOR____________________MOTOR
STARTER___________________MOTOR
PRESSURE TRANSMITTER_____P TRANS
FLOW TRANSMITTER_________T TRANS
MUFFIN____________________GRINDER
GENSET___________________GENSET
EMG ______________________EMG
EYEWASH__________________EMG
BREAKER __________________PWR REG
FUSE______________________PWR REG
SWITCH____________________PWR REG
CIRCUIT BOARD______________PWR REG

**shg** · 02-04-2011, 05:40 PM

Welcome to the forum.

It's pretty easy, and easier yet if you post a workbook that shows your data layout.

**Kupaaq** · 02-04-2011, 07:01 PM

Thanks, I didn't realize I could attach anything. So here's one of the workbooks I'm working with (there are several villages I'm doing this for). The first sheet, "ATQ", has the equipment descriptions organized to show parent-child relationships among the systems, parts, and equipment. The color codes aren't important for this issue. Next to each description there's some info on each part - also not important for this classification issue.
The second sheet is some scratch work. The third sheet contains my attempt at this classification, along with some of the keywords and classes I want to use.
One last concern: some of the descriptions contain more than one of the keywords. Is there a way to highlight the formula cell (or make it stand out some other way) so that I can manually go in and classify the non-typical descriptions?
Any and all help is greatly appreciated. My project is in danger of going over-budget, and this would be a huge time-saver.

**shg** · 02-04-2011, 08:15 PM

Here's an example:

Please Login or Register  to view this content.

The stuff in col H was copied randomly from Sheet1. The formula in I2 and down is

=LOOKUP(999, SEARCH($E$2:$E$37, H2), $F$2:$F$37)

You are building a rod for your back in the way you are using columns to indicate assembly hierarchy on Sheet1. They all need to be in the same column, and then use another column with something like a WBS (1, 1.1, 1.2, 1.2.1) to indicate breakdown.