VBA: time difference in any single rows

[Nelson78]

Hello everybody.

I'm approaching a work, and I would like some good hints to proceed.
In the image an example (actually I have thousands of rows to elaborate).

https://imgur.com/a/AQwvOR3

In the scheme, for any single code I have elaborated a sort of timeline.

Now, I have to set something like this: the user, for instance, would like to know how many times BETA follows CREATION in the timeline, and the time average beetween the two steps: so, in B15 and C15 he has to write the name of the two steps.
Clikking on CALCULATE, the two requested informations should be reported in D15 and E15.

Could it be a logical way to set the work?
Do you suggest anything else?

Thank's in advance.

 

[Nine Zero]

hmmmm maybe try this out on the click of the button

Dim r As Range
Dim stepA, stepB As String
Dim counter As Integer
Dim timeSum, timeAvg As Long
timeSum = 0
counter = 0
stepA = Range("B15").Text
stepB = Range("C15").Text
For Each r In Range("C2:C11")
    If r.Value = stepA Then
        If r.Offset(, 2).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 1).Value - r.Offset(, -1).Value)
        End If
    End If
Next
timeAvg = timeSum / counter
Range("D15").Value = counter
Range("E15").Value = timeAvg

not tested ... just kinda winged it lol

 

[Nelson78]

hmmmm maybe try this out on the click of the button

Dim r As Range
Dim stepA, stepB As String
Dim counter As Integer
Dim timeSum, timeAvg As Long
timeSum = 0
counter = 0
stepA = Range("B15").Text
stepB = Range("C15").Text
For Each r In Range("C2:C11")
    If r.Value = stepA Then
        If r.Offset(, 2).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 1).Value - r.Offset(, -1).Value)
        End If
    End If
Next
timeAvg = timeSum / counter
Range("D15").Value = counter
Range("E15").Value = timeAvg

not tested ... just kinda winged it lol

I thank you because it could inspire me.
But something wrong happens: consider that STEP B could also be in the following cells instead in the column E, for example for the codes 1, 3 and 9 BETA is on the column G, so the correct quantity is 5 and not 2 as your macro says.

 

[Nine Zero]

ahhh ok i was wondering about that but forgot to ask... try this one now... i simply added that condition

Dim r As Range
Dim stepA, stepB As String
Dim counter As Integer
Dim timeSum, timeAvg As Long
timeSum = 0
counter = 0
stepA = Range("B15").Text
stepB = Range("C15").Text
For Each r In Range("C2:C11")
    If r.Value = stepA Then
        If r.Offset(, 2).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 1).Value - r.Offset(, -1).Value)
        End If
        [COLOR=#ff0000]If r.Offset(, 4).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 3).Value - r.Offset(, -1).Value)
        End If[/COLOR]
    End If
Next
timeAvg = timeSum / counter
Range("D15").Value = counter
Range("E15").Value = timeAvg

 

[Nelson78]

ahhh ok i was wondering about that but forgot to ask... try this one now... i simply added that condition

Dim r As Range
Dim stepA, stepB As String
Dim counter As Integer
Dim timeSum, timeAvg As Long
timeSum = 0
counter = 0
stepA = Range("B15").Text
stepB = Range("C15").Text
For Each r In Range("C2:C11")
    If r.Value = stepA Then
        If r.Offset(, 2).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 1).Value - r.Offset(, -1).Value)
        End If
        [COLOR=#ff0000]If r.Offset(, 4).Value = stepB Then
            counter = counter + 1
            timeSum = timeSum + (r.Offset(, 3).Value - r.Offset(, -1).Value)
        End If[/COLOR]
    End If
Next
timeAvg = timeSum / counter
Range("D15").Value = counter
Range("E15").Value = timeAvg

Now, following your pattern, I'm going to try considering thousand of rows and dozens of columns, with any possible relation beetween STEP A and STEP B (for example, STEP A on column S and STEP B on column AC).

 

[Nine Zero]

Awesome, yeah keep building on to it, im sure there are many ways of doing this but with my knowledge this is what i came up with so you can study the code and change it to your liking