Identifying Specific Time Segments in Excel-CodePudding

I have 10,000 rows of data in Excel and the first column is the time of a data recording (hh:mm:ss). I want to filter/extract (in the front end) only the rows where the readings are at least six consecutive seconds - these are signal strength recordings and I only consider readings of at least six consecutive seconds to be valid for my purposes. I would only want to extract the rows in green in the attached image. Separate ask - what if I wanted segments of only six to eight seconds? Thanks!

CodePudding user response：

With O365 (assuming no excel version constraints per tags used in the question) you can try the following in cell D1:

=LET(rng, A1:B32, ref, SCAN("", INDEX(rng,,1), LAMBDA(ac,a, IF(ac="", 1,
  IF((a - TIME(0,0,1)) = OFFSET(a,-1,0), 0, 1)))), size, ROWS(ref),
  seq, SEQUENCE(size), start, FILTER(seq, ref=1),
  end, VSTACK(DROP(start-1, 1), size), DROP(REDUCE("", start, LAMBDA(ac,s,
   LET(e, XLOOKUP(s, start, end), f, FILTER(rng, (seq >= s) * (seq <= e)),
    IF(ROWS(f) > 5, VSTACK(ac, f), ac)))),1))

Note: The same result can be achieved to avoid using the

Then adjust the input range (rng) to your real case.

Explanation

Using LET for easy reading and composition. The name ref identifies with 1, the start of each group of values with consecutive seconds of the first column from rng. It has the same number of rows as the input data.

We use DROP/REDUCE/VSTACK pattern to generate iteratively the output with the data that satisfies the conditions the group has more than 5 consecutive elements. Check the following answer of this question: how to transform a table in Excel from vertical to horizontal but with different length

Via REDUCE we iterate over all start group positions (start). For each start group position (s), finds the corresponding end group position (e) via XLOOKUP. Filter the range (rng) for rows (seq) between start (s) and end (e) rows via FILTER. Append the filter result (f) only if the number of rows is bigger than 5 via VSTACK.