Formula or Script to Identify Discrepancies in Data Set

[KDavidP1987]

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.

Context:
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.

Question:

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

Sincerely,

 

[SpillerBD]

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.