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

[mboswell28]

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_NAME	DATE	JOB STATUS			OWNR_NAME	DATE
DRIVER 1	9/19/2017	ACCEPTED			DRIVER 1	9/19/2017
DRIVER 1	9/19/2017	TIMEOUT			DRIVER 1	9/20/2017
DRIVER 1	9/19/2017	TIMEOUT			DRIVER 1	9/21/2017
DRIVER 1	9/19/2017	TIMEOUT			DRIVER 1	9/22/2017
DRIVER 1	9/19/2017	ACCEPTED			DRIVER 2	9/18/2017
DRIVER 1	9/20/2017	ACCEPTED			DRIVER 2	9/20/2017
DRIVER 1	9/20/2017	TIMEOUT			DRIVER 2	9/21/2017
DRIVER 1	9/20/2017	ACCEPTED			DRIVER 2	9/22/2017
DRIVER 1	9/21/2017	TIMEOUT			DRIVER 2	9/23/2017
DRIVER 1	9/21/2017	REJECTED			DRIVER 2	9/24/2017
DRIVER 1	9/22/2017	ACCEPTED
DRIVER 2	9/18/2017	TIMEOUT
DRIVER 2	9/18/2017	TIMEOUT
DRIVER 2	9/20/2017	TIMEOUT
DRIVER 2	9/20/2017	TIMEOUT
DRIVER 2	9/20/2017	TIMEOUT
DRIVER 2	9/21/2017	ACCEPTED
DRIVER 2	9/21/2017	ACCEPTED
DRIVER 2	9/21/2017	REJECTED
DRIVER 2	9/22/2017	TIMEOUT
DRIVER 2	9/22/2017	REJECTED
DRIVER 2	9/22/2017	TIMEOUT
DRIVER 2	9/23/2017	ACCEPTED
DRIVER 2	9/23/2017	TIMEOUT
DRIVER 2	9/23/2017	TIMEOUT
DRIVER 2	9/23/2017	TIMEOUT
DRIVER 2	9/23/2017	ACCEPTED
DRIVER 2	9/23/2017	REJECTED
DRIVER 2	9/24/2017	REJECTED
DRIVER 2	9/24/2017	TIMEOUT
DRIVER 2	9/24/2017	TIMEOUT

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

 

[kvsrinivasamurthy]

Used helper column D.

In D2, then drag down

=IF(C2<>"TIMEOUT",ROW(),IF(C1="TIMEOUT",D1,ROW()))

In G2, then drag across upto J11
This is ARRAY formula, to be confirmed with Shift+Ctrl+Enter keys together.

=IFERROR(INDEX($B$2:$B$32,SMALL(IF(FREQUENCY(IF($A$2:$A$32=$F2,$D$2:$D$32,""),ROW($D$2:$D$32))>=3,ROW($D$2:$D$32),""),COLUMNS($G$2:G2))-ROW($A$2)+1),"")


F G H I J
OWNR_NAME DATE1 Date2 DATE3 DATE4
DRIVER 1 9/19/2017
DRIVER 2 9/18/2017 9/23/2017
DRIVER 3
DRIVER 4
DRIVER 5
DRIVER 6
DRIVER 7
DRIVER 8
DRIVER 9
DRIVER 10

 

[mboswell28]

Hello, and thanks for responding. I don't think the formula is doing what I intended. I tried editing the post to remove possible confusion but was unable. The first three columns are the data table. Columns 4 and 5 are a de-duplicated list of the drivers and the dates in which they were active. I thought that would be needed to set the conditions. I tried using the formula you provided and it gives me dates in columns G through J. What I'm really after is the date that the Driver had 3 consecutive TIMEOUT's and the count of the remaining statuses after the third consecutive TIMEOUT on that day. Does that help?

 

[kvsrinivasamurthy]

Sorry, still I am not clear about required result. p upoad a picture of expected result for the given data.

 

[mboswell28]

Sure I will try and upload a picture. The desired result is that for every driver I want to check on everyday that they have entries I want to know if on that day they at least three consecutive TIMEOUT's. For those drivers that meet that condition I then want to know how many more statuses they had on that same day after the third consecutive TIMEOUT. So for example Driver 1 on 9/19 met the three consecutive TIMEOUT condition and had one last status on that same day. For that same driver we would want to check on the next day 9/20 (the condition restarts) if this happened again until there are no more entries for that driver. We would then move on to the next driver and check the 3 consecutive TIMEOUT's condition for everyday listed for the next driver and how many statuses happened on that day the condition was met.

 

[mboswell28]

Here is a link.

View file

 

[kvsrinivasamurthy]

Does UDF will do.

 

[mboswell28]

Apologies I'm not sure what you're asking. What do you mean by UDF? Is that a file type?

 

[kvsrinivasamurthy]

UDF is user defined function. It can be used like functions in excel. It will have code written in module (like macro code).

 

[mboswell28]

That would be great. Thank you.