Since you don’t seem to have a problem using helper columns, this can be done in a fairly straightforward way, given that your data is already sorted by date.
RAW DATA TAB
Add a new column (we’ll call it column X) which checks to see if your cell is the first cell which starts a string of 5 date stamps, all of which being, as you say, 2 minutes apart [starting in X2, ending at an assumed X100 assuming the datestamp is column A, and the reference code in decimal is column B]:
=COUNTIFS(A2:A$100,">=" & A2 - TIMEVALUE("00:02:00"),B2:B$100,B2)
This counts how many cells below the current cell are no more than 2 minutes later, including itself, and also have the same code in column B. We will use this to check whether that cell starts a new string of 5 + identical, near-in-time, codes.
In Column Y, starting at Y2, put:
=IF(AND(OR(B2<>B1,A2 - TIMEVALUE("00:02:00")>A1),X2 >= 5), TRUE)
This will first check if either (1) the code in the current cell doesn’t match the code in the previous cell; OR (2) the time of the current row is at least 2 minutes later than the last line (either way, this is a new cycle). Then we check for the AND condition of whether the current row in column X shows a match of at least 5 cells below with the same code in the same time cycle. If TRUE, then it will return TRUE. Otherwise, it will return FALSE.
Then the code in column Z returns the number of the nth “hit code” we’re on for that row. ie: whether this is the 1st, 2nd, nth time that a string of 5 codes has been hit [starting in Z2; hardcode Z1 as “0”, or do some other special case so the first one won’t add the title of the cell above, resulting in a #VALUE! error]:
=IF(Y2,Z1+1,Z1)
This will turn Z into an ascending list of positions, repeating values whenever a NEW code has failed to be created. Now we need to grab the description of the code that this row represents.
Assume you have an ordered list of all your codes, where column 1 would be equivilent to “1000000…”, column 2 would be equivilent to “01000000..” etc. Name that single-row column (or, single column row) as a Range, which I will call Code_Index.
In column AA, starting at AA2, put the following:
=INDEX(Code_Index,SEARCH("1",C2))
This checks at which character “1” appears in column C at that row, and that becomes the position we want to pull the description from (which we have placed in the named range Code_Index).
Finally, we need to add a row which checks to see when a specific block of 5+ codes ends. Say, AB:AB (I forgot about this initially, hence its a little out of order initially). In AB2 and copied down, you will check to see whether there are at least 5 rows in a row of the same thing, within the 2 minute block, and also whether the next row is the same thing, within a new 2 minute block.
=IF(AND(COUNTIFS(B$1:B2,B2,A$1:A2,">"A2 - TIMEVALUE("00:02:00")>=5,OR(B3<>B2, A3 + TIMEVALUE("00:02:00")>= A2)),MAX(Z$1:Z1),"")
RESULTS PAGE
Now assume that’s all on Sheet1, and you want your ‘clean’ results on Sheet2.
In sheet 2, have column A be an index, which simply starts at 1 on A2 & adds 1 each row afterwards. Column B will be another ‘helper’ column, column C will pull the description, column D will pull the start time, and column E will pull the end time.
In column B, put the following formula, which will check which ‘new’ index we’re on (from column Z on the last tab). Starting at B2,
=MATCH(A2,Sheet1!Z:Z,0)
This will find the first row from the raw data tab which matches the current index number on A1. Then simply use this in an index formula in each of the next 3 columns, to pull the description, start time, and end time.
In C2 (pulling the description from AA on the last tab):
=INDEX(Sheet1!AA:AA,B2)
In D2 (pulling the start time from A on the last tab)
=INDEX(Sheet1!A:A,B2)
In E2 (pulling the end time from A on the last tab, *based on the row number from the column AB index)
=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!AB:AB,0))
Let me know if I’ve misconstrued how you want your “2 minute time blocks” set up; do some rigorous testing to make sure it acts the way you expect.
9
solved Searching column for consecutive values and returning three different values to other cells