• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help finding Sequential Duplicates.


New Member
I work as an HR and have to get along with the attendance of people working.
During the Month-end I have to figure out Maximum continuous Not Marked Attendance for employees.
Below is an example of the same:

01-09-201902-09-201903-09-201904-09-201905-09-201906-09-2019Count of Max. Consecutive not marked.
PresentNot MarkedNot MarkedNot MarkedAbsentNot Marked3

Is there any way that I can populate the Maximum Consecutive Not Marked in Last cell of given Range.

Thanks in Advance.


Active Member
How about
=MAX(FREQUENCY(IF($A6:$F6="Not Marked",COLUMN($A6:$F6)),IF($A6:$F6<>"Not Marked",COLUMN($A6:$F6))))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter