Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Excel to find trends and patterns: problem management

    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.
    Attached Files Attached Files
    Last edited by fulhamn; 2013-02-18 at 09:37. Reason: wrong attachment

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2013-02-19)

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..you could also add more 'keyword' columns by simply copying one of the existing columns, and then changing the keyword etc.

    zeddy

  9. #8
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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)?
    Attached Files Attached Files

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

  11. #10
    2 Star Lounger
    Join Date
    Jun 2006
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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?

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Using a filter with VB

    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.

    Filter.jpg

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

  14. #13
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Attached Files Attached Files
    Regards
    Roger Govier
    Microsoft Excel MVP

  15. The Following User Says Thank You to Roger Govier For This Useful Post:

    Maudibe (2013-02-25)

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Roger,
    I very much agree. Thanks

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •