Counting continuous duplicates

[exceluser9]

Hi Team,
I have a below data and we would want to know how many time word P has repeated continuously for each name. I have give the result which I require for Jack it should be 4 (Its repeated 4 times continuously) and for Jill it should be 2 (Its repeated 2 times continuously) and for James it should be 1.

Names	Attendance	Result
Jack	P	4
Jack	P	4
Jack	P	4
Jack	P	4
Jack	S	4
Jack	N	4
Jack	P	4
Jack	P	4
Jack	P	4
Jill	N	2
Jill	F	2
Jill	H	2
Jill	P	2
Jill	P	2
Jill	A	2
Jill	L	2
Jill	P	2
James	P	1
James	A	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Here is a non-VBA solution that uses a "helper" column.
Let's say that your data above is in cells A1:C20 (where row one is your header).

Then in column D, we want to track our consecutive counts of P for a person.
Do that by entering this formula in cell D2 and copy down to D20:

=IF(B2="P",IF(A2=A1,D1+1,1),0)

Now, to get the max values like you have shown in your example, enter this formula in E2 and copy down to E20:

{=MAX(IF(A$2:A$20=A2,D$2:D$20))}

Note that the { and } symbols really are not part of the formula. This indicates that this is an Array formula, and you must enter it using CTRL+SHIFT+ENTER, instead of just ENTER.

If you do not want to see the helper column (D), you can elect to hide it.

 

[exceluser9]

Thank you it worked.

Can you explain me the logic please.

 

[Joe4]

The Helper column is just a counter for consecutive "P" records. It says that if the record is a "P", and the name is the same as the one above, add one to the counter.
If the record is a "P", but the name is not the same as the name above, then return 1 (as that is the first "P" record for that person).
Otherwise, return a 0 (for all other non-"P" entries).

Then, we just need to take the MAX values of this helper column for each user to get what you want. That is what the other formula does.
For an expanded explanation on how that works, read this: https://exceljet.net/formula/max-if-criteria-match