V-Look, Search, or If Then Statements

**dmccart7** · 09-14-2011, 05:25 PM

Help, I am hopelessly stuck on this.

I have a list of 9500 line items with uncleansed/normalized descriptions that need to be categorized into generic commodity groupings, (electrical, plumbing, mechanical, janitorial, office supplies etc.). There is a key word in each items desription that can be searched on, but the it is not used consistently (i.e. MOTOR, MTR, MTRS or GEAR, GEARBOX, GEARMOTOR) or where it is positioned within the description. Examples shown below:

Column A
GEAR BOX ALPHA LP 120-M01-005-1 N152910
GEARBOX ALPHA LP090-M01-010-1 N142810
GEAR,HELICAL,15,0.75,1.64,10,14.5
GEARMOTOR,CBN3122SB310MT143T-1,BRWNNG MF
MTR,AC,098016.00,LEESON ELECTRIC
MTRS,NON-ELEC,103-1614-012,EATON
Z015310 MOTOR, BRF200C-217
M7284C-1000 MODUTROL MOTOR
Z015243 CONTROL VALVE, SPF11000/1050-1599
VLV,ACC,3028474,WAUKESHA CHERRY BURRELL
My questions are multiple:
1.) can a search for all the variations of those key words (Gear or Motor or Valve) be done with a single search statement for each key word? Or do individual search statements need to be used with each variation of the key words?
2.) Once the key word is found, can the categorization associated with that key word (motor = electrical, gear = mechanical, valve = plumbing etc.) be entered automatically in the next columnn? Examples shown below:

[B] Column A[ Column B
GEAR BOX ALPHA LP 120-M01-005-1 N152910 Mechanical
GEARBOX ALPHA LP090-M01-010-1 N142810 Mechanical
GEAR,HELICAL,15,0.75,1.64,10,14.5 Mechanical
GEARMOTOR,CBN3122SB310MT143T-1,BRWNNG MF Mechanical
MTR,AC,098016.00,LEESON ELECTRIC Electrical
MTRS,NON-ELEC,103-1614-012,EATON Electrical
Z015310 MOTOR, BRF200C-217 Electrical
M7284C-1000 MODUTROL MOTOR Electrical
Z015243 CONTROL VALVE, SPF11000/1050-1599 Plumbing
VLV,ACC,3028474,WAUKESHA CHERRY BURRELL Plumbing

3.) Is the "finding" statement a separate action from the "association/populating" statement or can both actions be written into a single statement?

All assistance on getting the correct statements to perform the above actions, would be greatly appreciated.
Dan

**TonyB51** · 09-14-2011, 08:12 PM

Hi,

I have used an extraction method that may suit using ISNUMBER with FIND functions.

Unfortunately, your data has conflicts.

GEARMOTOR shows both Mechanical and Electrical.

See whay you think.

Cheers

TonyB

**dmccart7** · 09-21-2011, 01:05 PM

Tony,
Thank you for responding and your prompt reply my apologies for not getting back to you very quickly, I have been traveling on business and did not have the time available to revisit my question here.

If you would not mind, I would like to talk to you about this. From your response I realized I did not express my dilemma/question succinctly (I was to brief) to recieve a proper response. Talking to you personally will greatly add to your understanding of what I am trying to do. Can we set up a date and time where you would be able to ring me up at 817-741-2924?

I am located in Fort Worth TX, USA.
Best regards,
Dan McCarthy