I need some assistance with pattern analysis (preferably in MS-Excel).
- I've released an electronic survey and anticipate several thousand responses.
- The survey contains a group of questions (i.e., 24) for which a 7-point Likert scale is used.
- I anticipate that more than 95% of those collected surveys will be valid. That is, users have identified their actual opinions.
- However, there's a slight chance that some users arbitrarily selected values. These would be considered "careless" responses and should be deleted before conducting statistical analysis.
Problem Background (patterns):
- In the event that careless responses will exist, some may follow obvious patterns. These could be in the following form:
-- All 24 answers have the same Likert scale values (e.g., "only 1s" or "only 6s")
-- All 24 answers are either in repeated ASC or DESC order (e.g., "1, 2, 3, ..., 7, 1, 2, 3, ..., 7,..." or "7, 6, 5, ..., 1, 7, 6, 5, ...).
Assuming these patterns do exist, they could be easily identified in a database (e.g., MS-Access) and then deleted from the dataset.
However, there may be other patterns (e.g., several 3s, followed by several 4, followed by several 7s, etc. etc.) that could not be easily predicted/identified through automation.
This is where I need some help... please see attached XLS that illustrates some examples of possible responses patterns.
For those -- with a red comment -- is there a way that some VBA script might detect a suspicious response? This is not to say it really is... however, given that several thousands survey would have to be reviewed, any automation would certain help to streamline this pre-analysis process.
Thoughts/recommendations for a VBA routine?