Longest streak by person in date range

[TyStolp]

Excel community,

I need help with this problem I have been trying to figure out for a few days. I have a spreadsheet with the following:

Column A: Y or N or NotRcvd or Blank. This determines whether that job was completed on time or not.
Column B: Persons Name. This shows who completed the job. There are about 12 different names in this column.

Column C: Date of Job. I have dates going back more than a year.

I would like to know the longest streak by a specific person in a specific date range. For example if one the people in column B is "John". I would like to know what the longest streak of "y"'s in a row in column A that "John" had between the date range of the previous 12 months.

Any help would be much appreciated.

Thanks!

 

[jondavis1987]

I think you would need to do a pivot table and have filter values of date and person.

 

[jondavis1987]

it's not exactly what you're looking for, but it would certainly give you a good idea

 

[Snakehips]

TyStolp,

Maybe along these lines...

Excel Workbook
	A	B	C	D	E	F	G
1	Comment	Name	Date	*	Longest streak for	John	5
2	y	John	01/01/2016	*	*	*	*
3	N	jim	02/01/2016	*	*	*	*
4	y	Bill	03/01/2016	*	*	*	*
5	y	Bill	04/01/2016	*	*	*	*
6	y	John	05/01/2016	*	*	*	*
7	y	John	06/01/2016	*	*	*	*
8	y	John	07/01/2016	*	*	*	*
9	y	John	08/01/2016	*	*	*	*
10	Y	John	09/01/2016	*	*	*	*
11	N	John	10/01/2016	*	*	*	*
12	Y	Fred	11/01/2016	*	*	*	*
13	Y	Fred	12/01/2016	*	*	*	*
14	Y	Fred	13/01/2016	*	*	*	*
15	Y	Fred	14/01/2016	*	*	*	*
16	Y	Fred	15/01/2016	*	*	*	*
17	Y	Fred	16/01/2016	*	*	*	*
18	Y	Fred	17/01/2016	*	*	*	*
19	Y	John	18/01/2014	*	*	*	*
20	Y	John	19/01/2016	*	*	*	*
21	Y	bill	20/01/2016	*	*	*	*
22	N	John	21/01/2016	*	*	*	*
23	Y	John	22/01/2016	*	*	*	*
24	Y	John	23/01/2016	*	*	*	*
25	Y	John	24/01/2016	*	*	*	*
Sheet1

NB it is an Array formula and needs to be confirmed using Ctrl = Shift + Enter

Hope that helps.

 

[TyStolp]

Snakehips,

Thanks for your quick response! I think that got it close, but it still doesn't seem right. I tried your formula in my spreadsheet and it returns a number, but the number doesn't seem to be correct.

See images below. I have 2 sheets. The first sheet is a dashboard that has statistics on it. The second sheet has all of the raw data. I have filtered the second sheet to show "Carlton". As you can see Carlton has had more than "3" in a row, which is what the newly inputted formula is resulting in.

[/URL][/IMG]

[/URL][/IMG]
Thanks!