You are here:

Excel/Categorization using Excel VBA


Excel category example
Excel category example  
I have a large spreadsheet with many rows and a large number of values, entered in 3 columns, that I would like to assign codes to. I think the easiest way to do this is to use Excel VBA, which I don't know how to program and was hoping you could help.

In the attached example, there are values in the columns labeled Code 1, Code 2 and Code 3. In any given row, only one of the three columns will have a value entered.  Based on the value in any of the Code columns, I need to assign a new value in the column labeled "Category". So for example, if one of the three Code columns has the text "Honda" then the macro will enter the text "Car" in the Category column for that row.

In the actual spreadsheet, there are other columns with data that the macro should ignore.

I've run macros before, but will also need a short reminder as to how to copy the code into the macro editor, save and run it.  Thank you!



You will need to go into the VBIDE, Alt-F11, insert a module, menu Insert>Module, and then paste this code

Public Sub ProcessData()
'add more to categories in a comma delimited lists
Const CAT_CARS As String = "Honda,"
Const CAT_VEG As String = "Celery,Lettuce,"
Const CAT_FRUIT As String = "Apple,Mango,"
Dim Lastrow As Long
Dim i As Long, ii As Long

   Application.ScreenUpdating = False
   With ActiveSheet
       Lastrow = .UsedRange.Rows.Count
       For i = 2 To Lastrow
         For ii = 2 To 4
         If .Cells(i, ii).Value <> "" Then
         If InStr(CAT_CARS, .Cells(i, ii).Value & ",") > 0 Then
         .Cells(i, "E").Value = "Car"
         Exit For
         ElseIf InStr(CAT_VEG, .Cells(i, ii).Value & ",") > 0 Then
         .Cells(i, "E").Value = "Veggie"
         Exit For
         ElseIf InStr(CAT_FRUIT, .Cells(i, ii).Value & ",") > 0 Then
         .Cells(i, "E").Value = "Fruit"
         Exit For
         End If
         End If
         Next ii
       Next i
   End With
   Application.ScreenUpdating = True
End Sub

To run  it, just activate that sheet, and then click Alt-F8 to see your macros, select this macro, and hit the Run button.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Phillips


Can - VBA, Functions and formulas, PowerPivot, conditional formatting, data validation, charting


I have been using Excel for in excess of 20 yeras, and I am a 9 year Microsoft MVP

PASS UK Developer Group SQL Soton SQL FAQ

BSc Mathematics

Awards and Honors
Microsoft MVP since 2005

©2017 All rights reserved.