detect pattern in excel sheet

DanSir,

I solved this problem in two steps (see attached)

1) create a pivot table, rows as customer, column as date, data as count of date
2) use a custom function to count the 10 consecutive days (I am assuming no Saturdays or Sundays in your data list)

The function returns true or false, you need to give it the range to test (in this case the line in the pivot for a customer with all the dates. It simply steps along the range and adds one to the counter if there is a 1 in the cell, if not the counter is reset. If the counter get to 10 the true is returned, otherwise false.

Function Count10ConsecutiveDays(RangeToTest As Range) As Boolean

    Dim Counter As Integer
    Dim CurrentCell As Range

    Count10ConsecutiveDays = False
    Counter = 0

    If RangeToTest.Rows.Count = 1 Then
        For Each CurrentCell In RangeToTest
            If CurrentCell.Value = 1 Then
                Counter = Counter + 1
                If Counter = 10 Then
                    Count10ConsecutiveDays = True
                    Exit For
                End If
            Else
                Counter = 0
            End If
        Next CurrentCell
    End If

End Function

I could write a macro to sort the data by customer then date and loop through and output the clients where there are 10 consecutive days. Probably neater but I think the one here is easier to understand and you can eyeball to check!

Hope that helps.

Regards

David

Hi David!

Big Thanks!

I'll try to put this in my "real" sheet and will give you some feedback if it works!!

Cheers!!

It all works fine! You really saved my day!

Glad I could help.

Regards

David

DanSir,

This is not a problem or difficult but I need to know precisely how you want it.

Lets say 2 clients have the following pattern:

1-1-1-x-x-1-1-1-1-1-1-1-1-1-1-1-1-x-x-1-1-1-1-1-1-1-1-1-1-1-1-x-x (3,12,12)
1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 (27)

Would you want the result to be 2 and 1 or something else?

Regards

David

DanSir,

I have amended the custom function to return the number of groups of 10 or more rather than a true or false.

Regards

David