Filter for Weeks when week is incomplete

[Bilingual]

Hi, I want to create a formula which i can apply as a filter so i can limit my dataset to only show date where full weeeks are shown, could anybody help?

An example of the data sheet and how i want the filter to work:

Date	Weeknumber	Filter
15-10-18	42	1
16-10-18	42	1
17-10-18	42	1
18-10-18	42	1
19-10-18	42	1
20-10-18	42	1
21-10-18	42	1
22-10-18	43	0
23-10-18	43	0

<tbody>
</tbody>

 

[Peter_SSs]

Sorry, I'm not trawling through 65,000+ rows to work out exactly what is and isn't in there.
If you were doing the task manually, how would you determine if a particular full week is shown?
For example, what steps would you take or what checks would you do to see if week 5 in 2017 had a full week shown?

 

[Bilingual]

Hi Peter, i understand.

What i want as the optimal solution is that Excel counts unique number of recurrences of days in every week and validate for every row, whether all 7 days is present in this particular week or not.

 

[Peter_SSs]

The data is not sorted by date. 01-01-2017 is inserted in rows 2956:3031, right in the middle of 2017 week 5.
Having a week split like that week 5 is makes any solution quite tricky. Can the table be sorted by date?

 

[Bilingual]

Hi Peter, sure , one thing that might make it easier is that is only the last week where weekdays can be missing.

 

[Peter_SSs]

Hi Peter, sure , one thing that might make it easier is that is only the last week where weekdays can be missing.

Assuming that is correct (except for the first week too which you will see if you sort the table by date) then try this macro after doing the date sort.
I have made the assumption that the table will always cover more than 1 week in total.
This should add an extra column to the table but with 0 & 1 the opposite way around to previously. That is, 0 represents a full week and 1 represents a partial week at the top and/or bottom of the table.

Sub Full_Weeks()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, wk As Long
  
  a = ActiveSheet.Range("Tabel1[[#All],[Weeknr]:[Date]]").Value
  ReDim b(1 To UBound(a), 1 To 1) As Long
  wk = a(2, 1)
  i = 1
  Do Until a(i + 1, 1) <> wk
    i = i + 1
  Loop
  If a(i, 2) <> a(2, 2) + 6 Then
    For j = 2 To i
      b(j, 1) = 1
    Next j
  End If
  
  wk = a(UBound(a), 1)
  i = UBound(a) + 1
  Do Until a(i - 1, 1) <> wk
    i = i - 1
  Loop
  If a(i, 2) <> a(UBound(a), 2) - 6 Then
    For j = i To UBound(a)
      b(j, 1) = 1
    Next j
  End If
  With ActiveSheet.Range("Tabel1[#All]")
    .Offset(, .Columns.Count).Resize(, 1).Value = b
    .Offset(, .Columns.Count).Resize(1, 1).Value = "Full Week (0)"
  End With
  
End Sub