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.
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
Bookmarks