CODEWITHSUNDEEP
vbaexcel
Yi Ding
Yi Ding

Reputation: 11

Return multiple rows before and after the match row based on time span in Excel/VBA

I have the following kind of data:

+---------------+-------------------------+----
| time          |   item                  | line index number
+---------------+-------------------------+----
| 05:00:00      |                         |  1
| 05:00:01      | MatchingValue           |  2
| 05:15:00      |                         |  3
| 06:00:00      |   B                     |  4
| 06:01:00      |                         |  5
| 06:45:00      |                         |  6
| 07:00:00      | MatchingValue           |  7
| 07:15:00      |                         |  8
| 08:00:00      |                         |  9
| 09:00:00      |                         |  10
+---------------+-------------------------+

What I am trying to do is to extract multiple rows before and after the matching row with item == "MatchingValue", together with the matching row itself . Those returned multiple rows are within 15 minutes of the time where item == "MatchingValue"

For example, if I was searching "MatchingValue" in the 2nd column, I would like to get the results of rows 1, 2, 3 and 6, 7, 8.

I know that one can get the return of rows 2, 7 at the same time by using array formula (e.g. Index and Match).　but I really don't know how to use array formula for my own question.

I appreciate any assistance.

Upvotes: 1

Views: 83

Answers (1)

Máté Juhász
Máté Juhász

Reputation: 2326

Easiest way is to add a helper column and filter your data in place or just use a pivot table to get only the data you need.
Formula in your helper column: =or(b2="MatchingValue",countifs(b:b,MatchingValue,A:A,">=" & A2-1/(24*4),A:A,"<=" & A2+1/(24*4))>0)
Of course you can also write array formula to collect your data in a new range but considering your already complex criteria and variable number of results that would be really a complex formula.

Upvotes: 1

Related Questions