Count weekends in a row

[joslaz]

Hey guys!


I have a table with this format:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31.12.2017[/TD]
[TD]So[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01.01.2018[/TD]
[TD]Mo[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06.01.2018[/TD]
[TD]Sa[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]13.01.2018[/TD]
[TD]Sa[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14.01.2018[/TD]
[TD]So[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15.01.2018[/TD]
[TD]Mo[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]Mo[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Now I have this aim:

I have a table that lists all IDs.
Now I want to know, if somebody have any entrys 3 weekends in a row.
So, the guy above have worked 3 weekends in a row.

How can I get this informationen?
Especially, if there is a year change.


Does anyone have any idea?


Best regards

 

[Aladin Akyurek]

What is the result/outcome for the sample you posted?

 

[joslaz]

The ID has worked 3 weekends in a row, so it should be true.
Or what do you asking for exactly?

 

[Aladin Akyurek]

that is ıd = 1 who has at least 3 weekends in a row, right?

 

[joslaz]

Thats right

 

[Aladin Akyurek]

that is ıd = 1 who has at least 3 weekends in a row, right?

Thats right

Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:A8=1,IF(ISNUMBER(MATCH(C2:C8,{"Sa","So"},0)),ROW(C2:C8))),IF(A2:A8=1,IF(ISNA(MATCH(C2:C8,{"Sa","So"},0)),ROW(C2:C8)))))>=3

 

[joslaz]

Many thanks!

Is your approach also without a matrix-function possible?

Owing to a large number of rows (750k) it is very slowly...

 

[Aladin Akyurek]

Many thanks!

Is your approach also without a matrix-function possible?

Owing to a large number of rows (750k) it is very slowly...

What follow will still be slow with very large number of rows...

Book1
	A	B	C	D	E
1	ID	Date
2	1	31.12.2017	1Sun	1	TRUE
3	1	1.01.2018	1Mon	2	FALSE
4	1	6.01.2018	1Sat
5	1	13.01.2018	1Sat
6	1	14.01.2018	1Sun
7	1	15.01.2018	1Mon
8	2	1.01.2018	2Mon
Sheet1 (2)

In C2 enter and copy down:

=A2&TEXT(B2,"ddd")

[1] Either...

In E2 control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(C2:C8,D2&{"Sat","Sun"},0)),ROW(C2:C8)),IF(1-ISNUMBER(MATCH(C2:C8,D2&{"Sat","Sun"},0)),ROW(C2:C8))))>=3

[2] Or...

=MAX(FREQUENCY(IF(V(ISNUMBER(MATCH(C2:C8,D2&{"Sat","Sun"},0))),ROW(C2:C8)),IF(1-V(),ROW(C2:C8))))>=3

For the latter formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function