Count occurrence of pattern in excel column
Posted on 2015-01-22
I'm looking for a way to look through a column and determine the occurrences of specific patterns. The file contains test answer keys with several columns, the important ones are:
question number, answer, test name
As the file contains a list of many tests, the results need to be based per test, sort of like the subtotal option for "at change in X column"
There are two main patterns we need to identify:
- How many times answer X occurs consequentially
- How many times each answer choice occurs in the test
I would think maybe highlighting any time a specific answer occurs 4 or more times in a row would be one good output. And at the end of each test, a row that lists the counts of each answer choice.
At the moment, the answer choices are stored as numbers (ordinal rather than letters) as text. I've got a simple nested if formula to translate the numbers to corresponding letters, I would plan on running that prior to running the pattern analysis.