identify consecutive months

[CAAT]

Hi there

Please assist with the attached file.
I need to identify transactions accounts that have a TYPE that appears consecutively for 3 months or more within a 12 month period without any gaps in months. You will notice that row 8 appears 3 times however there is a gap on Dec-14, and therefore it is not correct as an exception.
My expected results are shown in the PIVOT TABLE TAB in column O.
The data details TAB is the source data that can also be used.

thank you

 

[r1c1]

A tricky one... Essentially this is a longest winning streak problem.

You can use this array formula in the pivot worksheet.

=MAX(FREQUENCY(IF(B3:M3=1,$B$1:$M$1),IF(B3:M3=1,,$B$1:$M$1)))>=3

For more on how this formula works, read below 2 articles:

http://chandoo.org/wp/2015/01/30/longest-winning-streak-problem/ (read the comments)
and
http://www.thespreadsheetguru.com/blog/2014/6/29/formulas-to-calculate-longest-current-win-streaks

 

[CAAT]

thanks

I think i am sorted