vba - Searching column for consecutive values and returning three different values to other cells

Question

I have a series of columns. One contains a time stamp for a data point, the next is the data point, the one following that is a conversion from decimal to binary for that data point, and the remaining columns are that binary string split into each bit. Each column has a title: "No Engine Speed", "Engine Derate", and so on.

Here's what I'd like to do, but don't have the skill with VBA/Excel to do. I'm trying to take all that information and put it into something that's more friendly to a reader.

So, for example this:

would get me this:

The description comes from the column titles, and the time range start/end would come from the first column. The error codes I'll get with an Excel IF function or some kind of VLOOKUP function based on the contents of the description column. What I need is a VBA code or set of Excel functions that will populate those description and time stamp columns for me based on the contents of those bit columns.

How I envisioning it working is as follows: each bit column is searched through, if a 1 is found and the four cells more more below it are also found to be 1s the date stamp of the first 1 and final 1 populate the Start/End times in the more readable report I'm creating. In addition to this, the column header is copied to the description field.

The reason I want to only get the time stamps if there are five or more 1s consecutively is that I want some time to pass before the state is considered to be an 'event'. A second condition I'd like to meet would be that the time stamps are reasonably close together (say, within 2 minutes of each other). This is why even though there is a '1' event in the picture I linked for "16-May-15 21:52:47" I excluded it from the second image I linked.

The numbers next to the time stamps (and the timestamps themselves) will change depending on when the user opens the workbook. Those columns are the result of a query to a database and change based on what the previous shift's start and end time were. As a side note, when copying the time stamps I know that you need to paste the cell value and not just a regular paste, otherwise you wind up copying a query array to the database. I don't know if that has any bearing on a coding solution but I thought it was worth mentioning.

I considered trying to use some type of VLOOKUP function, but what I found doesn't quite do what I want because it doesn't check if there's five events or more in succession. Any guidance or direction you all can provide would be greatly appreciated. I feel like I have an idea of how to do this but I'm new to VBA and my Excel abilities are not up to the task just yet, and my Googling isn't turning up what I need.

I hope I was clear in my explanation of what I'm trying to do, feel free to ask questions if I wasn't.

Thanks,

Dan

EDIT 1:

As Grade 'Eh' Bacon suggested, I asked an additional question related to this one that resulted in a solution better suited to my needs. It can be found here.

Answer 1

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.