Counting the frequency of consecutive values in a column with multiple conditions

M

mboswell28

New Member
Joined
Oct 9, 2014
Messages
8
I am looking for assistance with counting the frequency that TIMEOUT appears consecutively for each driver on each day. I'm looking for instances TIMEOUT appears 3 times consecutively for a driver on a given day. At that point I'd like to know the number of rows that appear after the 3rd consecutive TIMEOUT for that driver on that day. I've included that data table below.

For example Driver 1 on 9/19 had 3 consecutive TIMEOUT's and one row appeared after the 3rd TIMEOUT. Driver 2 on 9/23 had 3 consecutive TIMEOUTS's and there were two rows after the 3rd TIMEOUT. Any assistance is greatly appreciated.

TIMEOUT
OWNR_NAMEDATEJOB STATUSOWNR_NAMEDATE
DRIVER 19/19/2017ACCEPTEDDRIVER 19/19/2017
DRIVER 19/19/2017TIMEOUTDRIVER 19/20/2017
DRIVER 19/19/2017TIMEOUTDRIVER 19/21/2017
DRIVER 19/19/2017TIMEOUTDRIVER 19/22/2017
DRIVER 19/19/2017ACCEPTEDDRIVER 29/18/2017
DRIVER 19/20/2017ACCEPTEDDRIVER 29/20/2017
DRIVER 19/20/2017TIMEOUTDRIVER 29/21/2017
DRIVER 19/20/2017ACCEPTEDDRIVER 29/22/2017
DRIVER 19/21/2017TIMEOUTDRIVER 29/23/2017
DRIVER 19/21/2017REJECTEDDRIVER 29/24/2017
DRIVER 19/22/2017ACCEPTED
DRIVER 29/18/2017TIMEOUT
DRIVER 29/18/2017TIMEOUT
DRIVER 29/20/2017TIMEOUT
DRIVER 29/20/2017TIMEOUT
DRIVER 29/20/2017TIMEOUT
DRIVER 29/21/2017ACCEPTED
DRIVER 29/21/2017ACCEPTED
DRIVER 29/21/2017REJECTED
DRIVER 29/22/2017TIMEOUT
DRIVER 29/22/2017REJECTED
DRIVER 29/22/2017TIMEOUT
DRIVER 29/23/2017ACCEPTED
DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017ACCEPTED
DRIVER 29/23/2017REJECTED
DRIVER 29/24/2017REJECTED
DRIVER 29/24/2017TIMEOUT
DRIVER 29/24/2017TIMEOUT

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col><col></colgroup>
 
Sort by date Sort by votes

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
No, the value should be 2. See the table: Three TIMEOUTS for Driver2 and there are 2 statuses after the 3rd TIMEOUT.

DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017TIMEOUT
DRIVER 29/23/2017ACCEPTED
DRIVER 29/23/2017REJECTED

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Surprisingly tricky. I somehow feel there's a better way, but I haven't found it yet. A UDF might be easier to maintain. Nevertheless, here's a formula solution. It requires 3 helper columns.

ABCDEFGHIJK
1OWNR_NAMEDATEJOB STATUSOWNR_NAMEDATE# Rows after 3 TIMEOUTS
2DRIVER 19/19/2017ACCEPTEDDRIVER 19/19/20171263
3DRIVER 19/19/2017TIMEOUTDRIVER 19/20/2017790
4DRIVER 19/19/2017TIMEOUTDRIVER 19/21/201710110
5DRIVER 19/19/2017TIMEOUTDRIVER 19/22/201712120
6DRIVER 19/19/2017ACCEPTEDDRIVER 29/18/2017131413
7DRIVER 19/20/2017ACCEPTEDDRIVER 29/20/20170151715
8DRIVER 19/20/2017TIMEOUTDRIVER 29/21/201718200
9DRIVER 19/20/2017ACCEPTEDDRIVER 29/22/201721230
10DRIVER 19/21/2017TIMEOUTDRIVER 29/23/20172242925
11DRIVER 19/21/2017REJECTEDDRIVER 29/24/201730320
12DRIVER 19/22/2017ACCEPTED331000
13DRIVER 29/18/2017TIMEOUT
14DRIVER 29/18/2017TIMEOUT
15DRIVER 29/20/2017TIMEOUT
16DRIVER 29/20/2017TIMEOUT
17DRIVER 29/20/2017TIMEOUT
18DRIVER 29/21/2017ACCEPTED
19DRIVER 29/21/2017ACCEPTED
20DRIVER 29/21/2017REJECTED
21DRIVER 29/22/2017TIMEOUT
22DRIVER 29/22/2017REJECTED
23DRIVER 29/22/2017TIMEOUT
24DRIVER 29/23/2017ACCEPTED
25DRIVER 29/23/2017TIMEOUT
26DRIVER 29/23/2017TIMEOUT
27DRIVER 29/23/2017TIMEOUT
28DRIVER 29/23/2017ACCEPTED
29DRIVER 29/23/2017REJECTED
30DRIVER 29/24/2017REJECTED
31DRIVER 29/24/2017TIMEOUT
32DRIVER 29/24/2017TIMEOUT

<tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
H2=IF(OR(K2=0,J2< I2+2),"",J2-K2-2)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=MIN(IF(($A$2:$A$100=F2)*($B$2:$B$100=G2),ROW($A$2:$A$100)))}
J2{=MAX(IF(($A$2:$A$100=F2)*($B$2:$B$100=G2),ROW($A$2:$A$100)))}
K2{=MIN(IF((INDEX(C:C,I2):INDEX(C:C,J2-2)="TIMEOUT")*(INDEX(C:C,I2+1):INDEX(C:C,J2-1)="TIMEOUT")*(INDEX(C:C,I2+2):INDEX(C:C,J2)="TIMEOUT"),ROW(INDEX(C:C,I2):INDEX(C:C,J2-2))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I'll play around a bit to see if it can be simplified.
 
Last edited:
Upvote 0
The date range is in text format . Pl correct it to date format.

F G H I J K
OWNR_NAME 19-09-2017 20-09-2017 21-09-2017 22-09-2017 23-09-2017
DRIVER 1
DRIVER 2

In G2 then Drag across upto K3

=CountSpecial($A$2:$A$32,$F2,$B$2:$B$32,G$1,$C$2:$C$32,"TIMEOUT")

The code for UDF CountSpecial is

Code: 
Function CountSpecial(OwnerRng As Range, OwnerVal As String, DateRng As Range, DateVal As Long, StatusRng As Range, StatusVal As String)


Dim Cel As Range
Dim X As Long, Y As Long, CelRo As Long, DateNum As Long


For Each Cel In DateRng


X = X + 1
DateNum = DateRng.Cells(X, 1)


If DateNum > DateVal Then GoTo Line1


If DateNum = DateVal And OwnerRng.Cells(X, 1) = OwnerVal Then


    If StatusRng.Cells(X, 1) = StatusVal Then
    Y = Y + 1
    Else
        If Y < 3 Then
        Y = 0
        Else
        CountSpecial = CountSpecial + 1
        End If
        
    End If
    
End If
Next Cel


Line1:
If CountSpecial = 0 Then CountSpecial = ""


End Function

To Paste the Code To module

Click Developer --> Visual Basic

In the visual Basic window

Insert --> Module

Now Paste the code in the module
 
Last edited:
Upvote 0
Here is the image

OWNR_NAME19-09-201720-09-201721-09-201722-09-201723-09-2017
DRIVER 11
DRIVER 2 2

<colgroup><col span="6"></colgroup><tbody>
</tbody>

<colgroup><col span="6"></colgroup><tbody>
</tbody>
 
Upvote 0
I'm not sure how much simpler this is, but this formula solution only requires 1 helper column next to the raw data:

ABCDEFGH
1OWNR_NAMEDATEJOB STATUSOWNR_NAMEDATE# Rows after 3 TIMEOUTS
2DRIVER 19/19/2017ACCEPTEDDRIVER 19/19/20171
3DRIVER 19/19/2017TIMEOUTXDRIVER 19/20/2017
4DRIVER 19/19/2017TIMEOUTDRIVER 19/21/2017
5DRIVER 19/19/2017TIMEOUTDRIVER 19/22/2017
6DRIVER 19/19/2017ACCEPTEDDRIVER 29/18/2017
7DRIVER 19/20/2017ACCEPTEDDRIVER 29/20/20170
8DRIVER 19/20/2017TIMEOUTDRIVER 29/21/2017
9DRIVER 19/20/2017ACCEPTEDDRIVER 29/22/2017
10DRIVER 19/21/2017TIMEOUTDRIVER 29/23/20172
11DRIVER 19/21/2017REJECTEDDRIVER 29/24/2017
12DRIVER 19/22/2017ACCEPTED
13DRIVER 29/18/2017TIMEOUT
14DRIVER 29/18/2017TIMEOUT
15DRIVER 29/20/2017TIMEOUTX
16DRIVER 29/20/2017TIMEOUT
17DRIVER 29/20/2017TIMEOUT
18DRIVER 29/21/2017ACCEPTED
19DRIVER 29/21/2017ACCEPTED
20DRIVER 29/21/2017REJECTED
21DRIVER 29/22/2017TIMEOUT
22DRIVER 29/22/2017REJECTED
23DRIVER 29/22/2017TIMEOUT
24DRIVER 29/23/2017ACCEPTED
25DRIVER 29/23/2017TIMEOUTX
26DRIVER 29/23/2017TIMEOUT
27DRIVER 29/23/2017TIMEOUT
28DRIVER 29/23/2017ACCEPTED
29DRIVER 29/23/2017REJECTED
30DRIVER 29/24/2017REJECTED
31DRIVER 29/24/2017TIMEOUT
32DRIVER 29/24/2017TIMEOUT
33

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3​

Worksheet Formulas
CellFormula
D2=IF(AND(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,"TIMEOUT")+2=COUNTIFS($A$2:$A4,A2,$B$2:$B4,B2,$C$2:$C4,"TIMEOUT"),C2="TIMEOUT"),"X","")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
H2{=IFERROR(LOOKUP(2,1/(($A$2:$A$100=F2)*($B$2:$B$100=G2)),ROW($B$2:$B$100))-MATCH(F2&G2&"X",$A$2:$A$100&$B$2:$B$100&$D$2:$D$100,0)-3,"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
The previous code is having som defect. The corrected code is here

Code: 
Function CountSpecial(OwnerRng As Range, OwnerVal As String, DateRng As Range, DateVal As Long, StatusRng As Range, StatusVal As String)


Dim Cel As Range
Dim X As Long, Y As Long, CelRo As Long, DateNum As Long


For Each Cel In DateRng


X = X + 1


If OwnerRng.Cells(X, 1) = OwnerVal Then


DateNum = DateRng.Cells(X, 1)


If DateNum > DateVal Then GoTo Line1


If DateNum = DateVal Then


    If StatusRng.Cells(X, 1) = StatusVal Then
    Y = Y + 1
    Else
        If Y < 3 Then
        Y = 0
        Else
        CountSpecial = CountSpecial + 1
        End If
        
    End If
End If
    
End If
Next Cel


Line1:
If CountSpecial = 0 Then CountSpecial = ""


End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Calculating time frames
Replies
2
Views
115
myall_blues
myall_blues
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
205
etaf
etaf
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
360
mrshuck
M
P
  • Question
User to select data
Replies
2
Views
269
pastorkc
P
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
275
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,268
Messages
6,123,966
Members
449,137
Latest member
yeti1016

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top