Using Excel to find trends and patterns: problem management

Topic

New Reply

Hi all,

I am a problem manager, I look at repeat incidents to try and resolve underlying problems. Part of Problem management is proactive problem management. This is looking at trends and patterns to see if we have recurring incidents. We have a Service desk ticket system, I export incidents data in to Excel. At the moment I use Pivot tables / filters to try and pick up on trends / patterns, this is time consuming. Is there a way to configure a macro or a formula so Excel picks up on repeat words or repeat sentences? Unfortunately the current ticket system is archaic and is not very good at producing trends & reports.

Attached is an example of the information I extract from the service desk ticket system. I have added in some repeat incidents but worded them differently. As you will see the brief.description column is texted based and is nearly always unique. This is complete by the service desk agent at the time of creating the incident ticket. So this would be the column I would look for trends / patterns.

Your help would be appreciated.

Reply | Quote

Replies

Hi

In the attached file, I have added some ‘key words’ or ‘phrases’ in the header row that could be used with formulas.
You can change the ‘search words/phrases’ to suit.

Not sure whether this helps much, but it’s a start perhaps.

zeddy

Reply | Quote

Hi

In the attached file, I have added some ‘key words’ or ‘phrases’ in the header row that could be used with formulas.
You can change the ‘search words/phrases’ to suit.

Not sure whether this helps much, but it’s a start perhaps.

zeddy

Hi zeddy,

This is very helpful. This is a great start.

Reply | Quote

Hi

In the attached file, I have added some ‘key words’ or ‘phrases’ in the header row that could be used with formulas.
You can change the ‘search words/phrases’ to suit.

Not sure whether this helps much, but it’s a start perhaps.

zeddy

What is the basic formula, if I wanted to create a new spread sheet?

Reply | Quote

Hi

In the attached file, I have added some ‘key words’ or ‘phrases’ in the header row that could be used with formulas.
You can change the ‘search words/phrases’ to suit.

Not sure whether this helps much, but it’s a start perhaps.

zeddy

Hi,

Is it possible to use the same formula but display it in column form and give you a total number of the times the key word in mentioned in the whole document. If you look at Sheet1 for the example. Can to formula cross reference to a different workbook (Tab)?

Reply | Quote

Hi

There are so many different ways of doing that, including using pivot tables.

One example using simple formulas is shown in the attached file.

zeddy

Reply | Quote

Hi

There are so many different ways of doing that, including using pivot tables.

One example using simple formulas is shown in the attached file.

zeddy

Hi That is great stuff. If I wanted to add (For example) more text in to the brief.description column, probably about 5000 rows worth. Is there a quick way to copy the formula for the entire Coolum? I don’t want to manual use the cell handle to drag the formula down thousands of lines?

Reply | Quote

Hi

You don’t have to drag the formula down thousands of rows.
there is a fast double-click way to do this:
First, add your 5000 rows worth of data in the brief description column.

Then go to the last row that have the formulas in (e.g. row 30).
Select the formula cells in this last row. (e.g. h30:m30)
Then, just double-click the bottom-right-corner tiny-black-square of the last formula cell
(The mouse cursor will look like + )

This will copy down all the formulas till it reaches the end of the description entries in the adjacent column G

zeddy

Reply | Quote

Is there a way to configure a macro or a formula so Excel picks up on repeat words or repeat sentences?

Fulhamn,

I have expanded on Zeddy’s sheet by creating a search box that begins to dynamically search the current sheet in the description column as you type the criteria into a textbox. If nothing is matching as you are entering characters, a message box appears to tell you so. If there are matching characters, then the cell becomes selected. The custom searchbox is activated simply by right clicking anywhere on the sheet.

It can easily be customized to meet your needs. Not really sure if this will help what you want to achieve.
HTH,
Maud

What is the basic formula, if I wanted to create a new spread sheet?

Assuming you mean a worksheet…

Code:

[COLOR=”#008000″]’CODE TO ADD A NEW WORKSHEET TO THE END[/COLOR]
Sub AddWorksheet()
     Worksheets.Add After:=Worksheets(Worksheets.Count)
End Sub

Reply | Quote

That looks really cool feature. How do I display the search box?

If I wanted to implement this search feature in another spread sheet, where do I start? Is it a case of starting VB and adding the code above?

Many thanks for your help.

Reply | Quote

just right click anywhere on page to open the search box. I will post the modules for you at the end of the day

Reply | Quote

Hi Maudibe/fulhamn

The search feature is really nice.

Now, the formulas I put in are intended to give you a method of counting the number of incidents based on the keyword currently placed in the header row 1. If you change the keyword in the header row (cols H, I, J, K), then the formulas will return a 1 or a blank. Perhaps you could shift the data down a few rows, then you could put SUBTOTAL formulas at the top of cols H, I, J, K etc to give you a count.
These totals will help you track the frequency of certain issue types.
Try changing one of the header keywords, e.g. change “server” for “interface”, you will then see the formulas working.

zeddy

Reply | Quote

..you could also add more ‘keyword’ columns by simply copying one of the existing columns, and then changing the keyword etc.

zeddy

Reply | Quote

Combined with zeddy’s concept (ingenious, would have never thought of that), you should have no problem finding what you need especially if the list is very long. Here is the code if you ever want to apply it to another workbook

Code:

[COLOR=”#008000″]’PLACE IN THE WORKSHEET MODULE THAT CONTAINS THE DATA[/COLOR]
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
UserForm1.Show
End Sub

Code:

[COLOR=”#008000″]’PLACE IN A USERFORM1 WITH A TEXTBOX, A LABEL, AND 2 BUTTONS[/COLOR]
Private Sub CommandButton1_Click()
     UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
     TextBox1.Value = “”
End Sub

Private Sub TextBox1_Change()
     TextBox1.SetFocus
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      On Error GoTo errorhandler
      Cells.Columns(7).Find(What:=TextBox1.Value, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
      Exit Sub
      errorhandler:
      MsgBox “There are no matches found”
End Sub

Reply | Quote

As an alternative, by placing a textbox on the sheet, you can type in any word/phrase then doubleclick. The rows will be filtered by the occurence of the entry in column G. To return to all the rows being visible, simply deselect the filter icon in the Menu.

33236-Filter

Code:

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:=”*” & TextBox1.Value & “*”
End Sub

Reply | Quote

Maud
That’s a nice solution.
However, I prefer to avoid the addition of a Text box.
It can be done with entry to a given cell (I used G1) and the Worksheet change event.
I think this “looks a little neater”, and just deleting the entry in G1 returns to the full list, as opposed to having to clear the filter.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Application.ScreenUpdating = False
    If [G1].Value = "" Then
        ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7
    Else
        ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7, Criteria1:="*" & [G1].Value & "*"
    End If
    For Each c In Range(Cells(2, 1), Cells(2, 6))
        c.AutoFilter Field:=c.Column, Visibledropdown:=False
    Next
    Application.ScreenUpdating = False
    [G1].Activate
End Sub

Reply | Quote

Roger,
I very much agree. Thanks

Reply | Quote

Roger

Nice solution but perhaps it would be better to specifically test for a change in cell [g2] rather than have the event routine run every time ANY cell was changed on the sheet.
see attached.

zeddy

Reply | Quote

Quite right, Zeddy. Very sloppy on my part!!!

However, I normally have Option Explicit at the top of all modules I create (here I was just modifying one from Maud), and therefore Dim all of my variables.
There is no need to have the extra variable zAddr, you can just check Target.Address directly

Here is the revised code

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [g1].Address Then
        Dim c As Range
        Application.ScreenUpdating = False
        If [g1].Value = "" Then
            ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7
        Else
            ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7, Criteria1:="*" & [g1].Value & "*"
        End If
        For Each c In Range(Cells(2, 1), Cells(2, 6))
            c.AutoFilter Field:=c.Column, Visibledropdown:=False
        Next
        Application.ScreenUpdating = False
        [g1].Activate
    End If
End Sub

Reply | Quote

Hi Roger

I normally don’t have Option Explicit, because it prevents me writing code ‘on the fly’.
I like to make up variables as I go.
I also confess that I rarely Dim my variables too.
In many simple spreadsheets there would be hardly any saving in filesize, execution time, and, as my wife says, most computers these days have lots and lots of ‘gigaberts’.
There are of course occasions when you absolutely MUST Dim certain variables, but generally VBA is very good at ‘coercing’ what you need.

For anyone starting with VBA coding, you should follow Roger’s advice and use Option Explicit and Dim your variables.

Now you are correct that you can use Target.Address directly in the test, but if you find yourself stepping through code when testing or debugging, I find it easier to use a variable to hold a temporary value I might want to look at.
So I use an interim variable like zAddr = Target.Address
So for example, if someone pasted a block of data onto the sheet (changing many cells at once, rather than just changing one cell), I can see exactly what the range is by looking directly at zAddr etc etc.

zeddy

Reply | Quote

Dynamically filter rows by description column (G) as you type in search. No need to click elsewhere to activate filter or to remove filter. Deleting the search criteria returns full list. Even cleaner with no filter icons showing. One line of code.

33258-filter2

Reply | Quote

Hi Maudibe

Very nice method of dynamically using the textbox for the search text!
I like that very much!

Instead of using textboxes to overlay and ‘hide’ autofilter dropdown arrows (nice trick), you can just use the
visibledropdown:=False
..property to turn them off.
(You can choose the columns you want for displaying/hiding the autofilter dropdown arrows.)
Roger’s code included hiding the autofilter buttons.

see attached file for my version.

zeddy

Reply | Quote

Cool Thanks for being helpful : }

Reply | Quote