Formula or Script to Identify Discrepancies in Data Set

KDavidP1987

Board Regular
Joined
Mar 6, 2018
Messages
51
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,
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,735
Messages
5,638,064
Members
417,001
Latest member
MSteel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top