Count weekends in a row

[joslaz]

Hey guys!


I have a table with this format:

ID	Date	Day	Value
1	31.12.2017	So	7
1	01.01.2018	Mo	8
1	06.01.2018	Sa	9
1	13.01.2018	Sa	8
1	14.01.2018	So	8
1	15.01.2018	Mo	7
2	01.01.2018	Mo	4

<tbody>
</tbody>

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