Find words within cell & put value in adjacent

Closed
Report
Posts
2
Registration date
Wednesday October 7, 2015
Status
Member
Last seen
October 8, 2015
-
Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
-
Column has Organisation Name Need to Search and Create a category to the next 5 columns

A Organisation can have key word in more than 1 category

Group of key words = category buy only 1 word need to be in cell to = category .

Roofing = category A
Painting, Contractor, Construction=Category B
Concrete, Paving = Category C
Sheet Metal, Welding = Category D
Reconstruction, Woodworks, General Contracting, = Category 5

Some categories may have some of the same key words


Anderson Ralph E Paintng Contractor
Anderson Ray V Company
Anderson Reconstruction
Anderson Rick
Anderson Roofing & Sheet Metal CO Inc
Anderson Roofing
Anderson Roofing
Anderson S C Inc
Anderson Scott Construction Corporation
Anderson`s Construction & Snow Removal
Andert Thomas Construction
Anderton Construction
Andra`s Construction CO
Andrae Construction CO Inc
Andre Don General Contracting
Andrew Construction
Andrew Pizzulo Construction
Anthony Michael Construction
Antler Construction
Anvab Inc
Apex Construction Inc
Apple Hill Woodworks
Applewood OrgName~Derry
Arbor Greene by Westminster Homes
Arch Tech Construction
Archer Western Construction CO
Archer Western Contractors Richland Chmbrs PMP STA
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archer Western Contractors
Archiart Interiors
Archies Construction
Architects Source Company
Architectural Accents
Architectural Builders
Architectural Coatings of Austin
Architectural Commercial Industrial Drafting Service Inc
Architectural Design

1 reply

Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
Grouchoed, Good morning.

I'm not sure if I understood well your question.

Try to use:

B2 -->
=IF(COLUMN()=IFERROR(LOOKUP(2,1/FIND({"Roofing","Painting","Contractor","Construction","Concrete","Paving","Sheet Metal","Welding","Reconstruction","Woodworks","General Contracting"},$A2),{2,3,3,3,3,3,4,4,5,5,5}),""),$A2;"")

Copy it right till F column.
Copy it down as necessary.
Use comma or semi-colon as your Excel version.

I did a Conditional Format to point you company names that don't have any category.

Take a look at my example to you:
http://speedy.sh/KrZMh/08-10-2015-ExcelForum-Company-Categorie-OK.xlsx

Is this what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0