Results 1 to 2 of 2

Thread: Formula or Script to Identify Discrepancies in Data Set
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    North Carolina
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Question Formula or Script to Identify Discrepancies in Data Set

    Hello all,

    I need to rely on your wisdom once more; This time for the solution to what will hopefully be a simply issue, that I'm simply drawing a blank on.

    I work within a Help Desk / Service Desk team for a company, and do metrics reporting. One of the systems I report on is a survey system which is ran through our primary ticketing system. If a user responds that their perception of the service was anything less than satisfied (neutral, dissatisfied) and an explanation wasn't provided then I follow-up with them to identify the reason so we can address it promptly. Unfortunately, many users put Very Dissatisfied by accident, and I end up having to make corrections. However, some users choose to resubmit their survey rather than let me know they were Very Satisfied so that I can correct it. This is where discrepancies can pop up.


    Is there a formula or VBA script I could use to identify lines of data where discrepancies exist in duplicate survey entries for incidents, so that I can correct the dissatisfied surveys to reflect their counterparts.

    The table is formatted as table, so column headers can be utilized in formulas. Table name is YTDSurveyData

    Columns Include: Survey Number, Survey Response, Incident Number

    Survey # = Survey # (Unique, like a primary key for survey)
    Survey Response = a formula which looks at numerical responses to the 7 questions of the survey, then reflects the lowest scoring answer as the overall survey response. (if lowest response is neutral, then Neutral; If Dissatisfied then Dissatisfied; etc)
    Incident Number = (This is where duplicates are identified). A formula which extracts the associated incident number from the survey description field.

    Need a field which looks for similar incidents numbers among the YTD surveys where the response field is different. Where one survey matching incident 123456 is marked Very Dissatisfied and another survey matching the same incident is marked Very Satisfied (or other variations of responses).


  2. #2
    Board Regular
    Join Date
    Jul 2014
    Memphis, TN
    Post Thanks / Like
    11 Post(s)
    1 Thread(s)

    Default Re: Formula or Script to Identify Discrepancies in Data Set

    Build a pivot table.
    Drop Survey Number in ROWS
    Drop Survey Response in COLUMNS
    Drop Survey Response in VALUES, should give Count of Survey Response.

    Set Report Layout to TABULAR
    Right Click on an Incident Number and choose FILTER, VALUE FILTERS. Set for "Is greater than" and value of 1.

    If you need the Survey number listed, drop iit into the ROWS below the Incident Number field label.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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