Count Consecutive Cell Matches

Good evening all,

I'm trying to create a formula to count the consecutive number of matches in a row of data. I've attached a copy of a template spreadsheet showing similar data for reference. The data is recorded data from some plant systems and taken in 1 minute increments. I'm trying to get a formula to see how many minutes the "steps" are taking. I've done the countif to get total number of say values with "3" but I'm looking to break down how long the "3s" repeat themselves and for each occurrence. So for example, in cells C2:C2762, the number "1" is present 45 times (or for 45 minutes) and I'd like to find out how long each stretch occurs. So is it 3 stretches for 15 cells each or 5 stretches for 9 cells, etc.

Hopefully that kind of makes sense but I can't wrap my head around the formula for finding that breakdown. If this was only for a few days of data it would suck but I could do it manually however this is for 4 months of data at 1440 rows per day.

Thanks for any help.

Hi

one of the forum gurus may be able to do this with formulas, but I have resorted to a macro.

This macro displays an inputbox for you to enter the value to check, then returns a message box with the number of sequences of that value and the number of times it occurs. It can very easily be tweaked to put the values in particular cells, if you prefer.

Please Login or Register  to view this content.

Thanks Nicky and Bo. I used both and they worked awesome for sorting through the 240k rows of data I had. I appreciate the help.

Thanks