Excel: Count number of duplicates in row for unique records
I'm looking at repeat enrollment data that varies between two text responses "enrolled" and "not enrolled" over 24 columns, each column represents a consecutive term. The dataset has over 1000 unique records. I want to count the number of patterns of each record repeating (eg. enrolled, enrolled, not enroll, enrolled, not enrolled, enrolled) enrolled = 2, not enrolled = 1, enrolled = 1, not enrolled = 1, enrolled = 1. Then assign each record to a group based on the frequency of specific patterns repeating (for the example above the record would be assigned to group "Two Stop Gaps" because over the course of all the terms they were not enrolled then enrolled again twice. I've included a snippet of the existing data (Columns A - J), the frequency counts I'd like generated (Columns K - S), and the assigned groups based on the frequency of each group repeating (Column T). I should point out that column T is the most important data output for my work.
I'm sure there is an easier way, I just can't seem to figure it out. Thanks in advance for any and all suggestions!
Example: Table Snippet of Data
Solution 1:
=SUMPRODUCT(--(A2:I2<>B2:J2))
will give you the number of transitions between different values in adjacent cells. So the number of “stop gaps” is
=INT(SUMPRODUCT(--(A2:I2<>B2:J2))/2)
and the number you want is
=MIN(INT(SUMPRODUCT(--(A2:I2<>B2:J2))/2), 3)