In one of my previous articles I explored how we might find the customer service issues hidden within the masses of text we receive on a daily basis through an online feedback form. In this article we will take a look at what we could do next once we know what the main customer issues are.
Acting on the Data
When we start to see trends forming that is when we can build categories and decide a system of actions for those categories. For instance, we might push certain new messages to our support departments to be expedited automatically, while other less pressing issues can sit in a queue for a little longer.
When we have a ‘security’ issue come through we want to send them quickly to be handled by our ‘security specialist’. If a message contains a request that deal with writing Macros we would want one of the developers to help with that.
Does this sound like something you would like to use?
Processing the Results
When the button is hit we will check for various categories (on a second sheet) and list these categories in the next column:
Sub WordCount() Dim vArray, WordIssue, ElementCounter As Variant Dim lngLoop, lngLastRow As Long Dim rngCell, rngStoplist As Range ElementCounter = 2 'setting a default value for the counter Worksheets(1).Activate For Each rngCell In Worksheets("Word").Range("A3", Cells(Rows.Count, "A").End(xlUp)) vArray = Split(rngCell.Value, " ") 'spliting the value when there is a space vrWordIssue = "" ElementCounter = ElementCounter + 1 'increases the counter every loop For lngLoop = LBound(vArray) To UBound(vArray) If Application.WorksheetFunction.CountIf(Sheets("Issue").Range("A2:A" & Sheets("Issue").UsedRange.Rows.Count), vArray(lngLoop)) > 0 Then 'this is to test if the word exist in the Issue Sheet. If vrWordIssue = "" Then vrWordIssue = vArray(lngLoop) 'assigning the word Else If InStr(1, vrWordIssue, vArray(lngLoop)) = 0 Then 'a binary of comparison vrWordIssue = vrWordIssue & ", " & vArray(lngLoop) 'this will concatinate words issue that exist in Issue Sheet End If End If End If Next lngLoop Worksheets("Word").Range("B" & ElementCounter).Value = vrWordIssue 'entering the final word issue list into cell. Next rngCell End Sub
How it Works
The macro starts by creating a word list for each cell in the list of feedback messages by splitting the cell contents whenever there is a space. This creates an array in memory that we can loop through to test if the word exist in the “Issue Sheet”.
If we find a word that we want to check for, we assign the word to another list for later insertion into the final issue column.
As you can see, the output of this is we take unstructured data and produce something we can actually work with. That’s huge. This technique can be used for much more than the simple example we demonstrated here but I hope it gives you some inspiration for how you can process the text in your work.
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter