Assign category to data row based on matched word in cell

You could do this with a (long) formula .........

Hi JLLatham,

Long formula? ;)))

Well, for me it would have been - formulas are not my strong suit.  Great solution you provided!

You are welcome :)))

hey bill,

thanks man, this is super helpful!

have a great day.

You could do this with a (long) formula - you need a test for each possible category, and I imagine there are or will be more than 3.

Here is a User Defined Function (UDF) that you could use if you don't mind a macro solution.  You use it just like you would a regular Excel worksheet function like SUM() or AVERAGE().  You need a list of all of your possible categories somewhere and to set the code up to find that list.  I set up a sheet named ListSheet (which could be hidden from view) and put my list in column A starting at row 2.  The routine automatically figures out how many items are in the list, so you can add to it or delete from it easily.

To use the UDF you simply type a formula into a cell such as

=GetCategory(C2)

where cell C2 has the description you want to get a category for.

You will need to save the workbook as a macro enabled (type .xlsm or .xlsb) workbook, and enable macros when you open it (or make it a trusted file or put it into a trusted location).

The code goes into a regular code module, and you can find instructions for getting that part of the job done here:

http://www.contextures.com/xlvba01.html#Regular

You need to edit the bold lines in the code below to match up with the name and layout of the sheet you decide to put your categories list onto.  The code:

Function GetCategory(theDescriptionCell As Range) As String

  'the name of the sheet that your list of categories is on

Const catListWS = "ListSheet" ' name on its tab

  'the column the list is in on that sheet

Const catCol = "A"

  'the first row with a category entry in it

Const firstCatRow = 2

  Dim catList As Range

  Dim anyCatEntry As Range

  Dim lastCatRow As Long

  Dim testPhrase As String

  Set catList = Worksheets(catListWS).Range(catCol & firstCatRow _

   & ":" & Worksheets(catListWS).Range(catCol & Rows.Count).End(xlUp).Address)

  GetCategory = "** New Category **" ' default to no result

  testPhrase = theDescriptionCell.Value

  For Each anyCatEntry In catList

    If InStr(1, testPhrase, anyCatEntry, vbTextCompare) > 0 Then

      GetCategory = anyCatEntry

      Exit For

    End If

  Next

  Set catList = Nothing

End Function