yogi_ Compute The Latest Length Of Consecutive String Of Yes Entries By Name In Column A

                                          Google Spreadsheet  Post  #979

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 18, 2013

user Norm Hamson said:(https://productforums.google.com/forum/#!mydiscussions/docs/1dzMO5qmRbY)
Formula for calculating current streak
I have a spreadsheet with three columns:  A contains a date, B contains a name and C contains Yes/No.

I would like to calculate the current streak of consecutive 'Yes' responses for each name.

Example
1/1/2013 John Yes
1/1/2013 Mike No
1/1/2013 Bart Yes
1/2/2013 John Yes
1/2/2013 Mike Yes
1/2/2013 Bart No

The current streak from John should be 2, Mike should be 1 and Bart should be 0.

Any ideas?

Thanks,
norm
---
Sure.

Larger sample of data:
1/1/2013 John No

1/1/2013 Mike No
1/1/2013 Bart Yes
1/2/2013 John Yes
1/2/2013 Mike Yes
1/2/2013 Bart No

1/3/2013 John Yes
1/3/2013 Mike Yes
1/3/2013 Bart Yes
1/4/2013 John Yes
1/4/2013 Mike No
1/4/2013 Bart Yes
1/5/2013 John Yes
1/5/2013 Mike Yes
1/5/2013 Bart No
1/6/2013 John Yes
1/6/2013 Mike No
1/6/2013 Bart Yes
1/7/2013 John Yes
1/7/2013 Mike Yes
1/7/2013 Bart No

Starting at the latest date, I want a count of consecutive 'Yes' answers.  The latest date is 1/7 (in this example).  Bart answered 'No' on 1/7, so his current 'yes' streak is 0 days.  Mike answered 'Yes' on 1/7 but 'No' on 1/6, so his current streak is 1 day.  John answered 'Yes' on 1/7, 1/6, 1/5, 1/4, 1/3, 1/2 and 'No' on 1/1, so his current streak is 6 days.

Does that make it more clear?

thanks,
norm
------------------------------------------------------------------------------------------
following is a solution using several helper columns