Excel/Categorization using Excel VBA
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
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"
ElseIf InStr(CAT_VEG, .Cells(i, ii).Value & ",") > 0 Then
.Cells(i, "E").Value = "Veggie"
ElseIf InStr(CAT_FRUIT, .Cells(i, ii).Value & ",") > 0 Then
.Cells(i, "E").Value = "Fruit"
Application.ScreenUpdating = True
To run it, just activate that sheet, and then click Alt-F8 to see your macros, select this macro, and hit the Run button.