Counting longest non-repeating streak

[paulrk]

Hi, I’d appreciate help to calculate a tourism streak or longest streak of different events done before one is repeated. I'm using Office 365 on a Windows PC.

In the example below I have entered events done and the order they were done in plus a third column with my manual entry of the count from the start of that streak until it is broken that I would like to replace with a formula. Once this calculation is automated I will be able to look up 6 as the max value.

Please note that the longest overall streak is 9 between repeats of Event A, but in the interim there were two visits to Event E that cuts short the streak to 6.

Any help greatly appreciated, although I’ve not used VBA before so I’m hoping for a formula-based solution if at all possible.

Thanks,

Paul.

 

[pgc01]

Hi Paul
Welcome to the board

Try this udf.

Function LongestStreak(r As Range)
Static dic As Object
Dim j As Long

If dic Is Nothing Then Set dic = CreateObject("Scripting.Dictionary")
dic.RemoveAll

For j = r.Rows.Count To 1 Step -1
    If Not dic.exists(r(j).Value) Then dic.Add r(j).Value, "" Else Exit For
Next j
LongestStreak = r.Rows.Count - j
End Function

Example. In B2:

=LongestStreak($A$2:A2)
Copy down

Book1
	A	B	C
1
2	a	1
3	a	1
4	b	2
5	b	1
6	c	2
7	d	3
8	e	4
9	c	3
10	f	4
11	g	5
12	h	6
13	i	7
14	j	8
15	g	4
16	i	3
17
Sheet1

Remark: If you are not used to udf's, check Rick's istructions in this post:

How to Count the complete Numbers in a single cell

Hi Dear members I want to count the complete numeric values from a single cell, I am using below formula but it not work perfectly as I want for example in Cell A1 I enter data as under 1 ali, 2 asad , shan, 10 aslam in the above data 1,2,10 is used as the serial numbers...

www.mrexcel.com

 

[paulrk]

Thanks, PGC! It works perfectly. I spent days staring at this problem and it was driving me to distraction. You've made my day and I'm very grateful

 

[pgc01]

You're welcome.
Thanks for the feedback.

 

[pgc01]

Remark: A simpler expression for the return value

replace:

LongestStreak = r.Rows.Count - j

with

LongestStreak = dic.Count