Web Analytics

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

What we need to do is have a button that launches our macro (download link) and processes the feedback messages.

When the button is hit we will check for various categories (on a second sheet) and list these categories in the next column:

The Macro

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
    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
                    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

About Rich

Rich is the owner and creator of Windows Guides; he spends his time breaking things on his PC so he can write how-to guides to fix them.

Free PC tips by email

Search Windows Guides


Computer tips in your inbox
Sign up for the Windows Guides newsletter to get PC tips and access to free Windows books (More details)

Subscribe now
Popular Guides

See which sites have been visited on your PC (even if private browsing mode is used)

Create a Windows 7 System Repair Disc

Best Free Anti-malware

Hibernate vs. Sleep vs. Shut-Down

i3, i5, and i7; Dual, Quad, Hexa Core Processors. How to they Differ?

Intel's Ivy Bridge Processor: new Features

Windows Guides on Facebook