Finding groups of duplicate adjacent cells in a column that meet criteria

[ljenning]

Hello all,

I have time and speed data. Each row has a vehicle speed recorded at every second, e.g

Sec Speed
1.....0
2.....2
3.....4
4.....3
5.....0
6.....0
7.....0
8.....3
9.....5
10....6
... ...

Most workbooks I'm working with have around 100,000 rows of data. I need a way to find a group of adjacent cells in the speed column that log no speed (0 mph) for more than 30 minutes i.e. find a total of 1,800 or more 0's in the speed column adjacent to each other, and how many times that occurs (including 0). I'm trying to write a macro that does that function. Overall I have about 80 workbooks that I would like to run this macro on. At the end I'll create a summary workbook that lists each vehicle (workbook) and how many times it has been idle for more than 30 minutes. I already have the scripts that can run a macro for every file in a directory and to create the summary, but not to count the adjacent cells.

Thank You
~lance

 

[Jobowo]

You can do this without a macro, or replicate this with a macro:

Assuming your data is in columns A and B, in C2 insert the formula
=IF(B2=0,C1+1,0)
and copy down

in D2 insert the formula
=COUNTIF($c:$c>=1800)

 

[ljenning]

That did it. Had to do some tweeking but got the final macro written out. Thanks.