Consecutive appearance

tlinht · Aug 2, 2014

From the chart below, I need a formula that return the maximum number of times 1&2 appears in this list consecutively, before another set of numbers appears. From this list the answer should be 3. The array formula that I’m using now however returns the value of 16. Any input would be greatly appreciated.

=SUMPRODUCT(MAX(FREQUENCY((AZ1:AZ16=1),--(OFFSET(AZ1,1,0,ROWS(AZ1:AZ16))=2)),ROW(AZ1:AZ16),(AZ1:AZ16<>1),--(OFFSET(AZ1,1,0,ROWS(AZ1:AZ16))<>2)))

1. 1
2. 2
3. 1
4. 2
5. 1
6. 2
7. 1
8. 4
9. 1
10. 2
11. 1
12. 4
13. 1
14. 6
15. 1
16. 2

Rick Rothstein · Aug 2, 2014

tlinht said:
Rick Rothstein, thanks for that tip. I'm a total newbie VBA, but would love to definitely look more into it.

You are quite welcome, I am glad I was able to be of some help. Yes, VBA is pretty neat once you know it... if you cannot think of a worksheet formula to do what you need, there is almost always a VBA solution that will work. One thing about my code though, I forgot to change some of the variable names so that they are more descriptively relevant. I originally developed the code for your original question (with the numbers 1 and 2), but expanded it to cover the more general situation when it became apparent that is what you ultimately wanted... and then I forgot to update the variable names. So here is my code, revised solely to make variable names more descriptive, other than that, the code structure is identical to what I posted originally. The renaming may help you and others reading this thread figure out the underlying logic to my code.

Code:

Function MaxConsecutives(VerticalRange As Range, ParamArray ConsecutiveNumbers() As Variant) As Long
  Dim N As Long, CombinedColumn As String, CombinedValues As String, Sequences() As String
  CombinedColumn = UCase(Application.Trim(Join(Application.Transpose(VerticalRange.Value), Chr$(1))) & Chr$(1))
  CombinedValues = UCase(Join(ConsecutiveNumbers, Chr$(1)) & Chr$(1))
  N = 1 + VerticalRange.Rows.Count / 2
  On Error GoTo NoValuesFound
  Do
    N = N - 1
    Sequences = Split(CombinedColumn, Application.Rept(CombinedValues, N))
  Loop While UBound(Sequences) = 0
  MaxConsecutives = N
NoValuesFound:
End Function

tlinht · Aug 3, 2014

With the MaxConsecutives UDF you provided Rick, is there a way to implement an offset. For example instead of

=MaxConsecutives(A2:A15,"First","Second")

First
Second
Second
Third
First
Second
Second
Third

Is there a way you could you use an offset the following:

First
Second
Second
Third
First
Second
Second
Third

Rick Rothstein · Aug 3, 2014

tlinht said:
With the MaxConsecutives UDF you provided Rick, is there a way to implement an offset. For example...

Is there a way you could you use an offset the following:

First
Second
Second
Third
First
Second
Second
Third

I am not sure I understand what "consecutive" would mean for the above. I would think the two offsetted "First/Second" text you show are not consecutive because of the red text between them. To be consecutive, I would think they should appear as below...

First
Second
Second
First
Second
Second

Please clarify your intent for us. Also, I am guessing the green text shown below do not have to be the same word as you showed originally, correct?

First
Second
Second
Third
First
Second
Second

tlinht · Aug 3, 2014

My apologies for the confusion. Here are probably some better examples:

First First First
Second SecondSecond
First Second Second
Second Third Second
First First Third
Third Second First
First Second Second
Second Third Second
First Second
Second Third
Third First
Second
Second
Third

So from the examples above the answer I'm seeking is 2 for the max consecutive. The key out of the pair (First, Second) would be "first". Since First,Third appears it stops the current max at 2. Instead had there been another (First, Second), in all three examples, it would have extended the max to 3. So in essence I'm looking for max consecutive pairs of (First,Second) in their respective sequences.

Rick Rothstein · Aug 3, 2014

tlinht said:
My apologies for the confusion. Here are probably some better examples:

First First First
Second SecondSecond
First Second Second
Second Third Second
First First Third
Third Second First
First Second Second
Second Third Second
First Second
Second Third
Third First
Second
Second
Third

So from the examples above the answer I'm seeking is 2 for the max consecutive. The key out of the pair (First, Second) would be "first". Since First,Third appears it stops the current max at 2. Instead had there been another (First, Second), in all three examples, it would have extended the max to 3. So in essence I'm looking for max consecutive pairs of (First,Second) in their respective sequences.

I am sorry, but I am still confused. Is each row above in a single cell or multiple columns (for example, is "First First First" in cell A1 or in cells A1, B1, C1)? I am sure I will have a follow up question, but I need to understand the layout of your data first.

tlinht · Aug 3, 2014

My apologies for the confusion the last example wasn't spaced properly. Here is how the chart should look.

First
Second
First
Second
First
Third
First
Second

First
Second
Second
Third
First
Second
Second
Third
First
Second
Third
First
Second
Second
Third

First
Second
Second
Second
Third
First
Second
Second
Second
Third
First
Second
Second
Third

[FONT=&quot]So from the examples above the answer I'm seeking is 2 as the max consecutive. The key out of the pair ([FONT=&quot]first, second[/FONT]) would be "first". Since First,Third appears it stops the current max at 2. Instead had there been another [FONT=&quot]first, second[/FONT], in all three examples, it would have extended those respective current max's to 3. So in essence I'm looking for max consecutive of the pair ([FONT=&quot]first,second[/FONT]).[/FONT]

tlinht · Aug 3, 2014

Rick, were the examples I provided a little bit clearer? And to answer your last question, each value in separate cell.

Rick Rothstein · Aug 3, 2014

tlinht said:
My apologies for the confusion the last example wasn't spaced properly. Here is how the chart should look.

First
Second
First
Second
First
Third
First
Second

First
Second
Second
Third
First
Second
Second
Third
First
Second
Third
First
Second
Second
Third

First
Second
Second
Second
Third
First
Second
Second
Second
Third
First
Second
Second
Third

So from the examples above the answer I'm seeking is 2 as the max consecutive. The key out of the pair (first, second) would be "first". Since First,Third appears it stops the current max at 2. Instead had there been another first, second, in all three examples, it would have extended those respective current max's to 3. So in essence I'm looking for max consecutive of the pair (first,second).

Are you saying the maximum consecutives is 2 for each of the three examples? If so, then I do not understand your definition of consecutive... it looks like "Third" breaks the consecutiveness of the sequence.

tlinht · Aug 3, 2014

Yes the max would be 2. Ok well how about I reword it this way, I'm looking for the max times (first,second) appears in a row before another set of numbers appears but it has to contain (first,third/fourth/fifth/etc), anything other than (first,second), which would continue the streak. I just used (first,third) as an example to break the steak.

tlinht · Aug 4, 2014

Rick I might just have a better explanation for a similar problem. If its not too much to ask, would you mind taking a look at it, I've provided a link.

http://www.mrexcel.com/forum/excel-questions/796486-help-user-defined-function.html

Consecutive appearance

tlinht

New Member

Rick Rothstein

MrExcel MVP

Excel Facts

tlinht

New Member

Rick Rothstein

MrExcel MVP

tlinht

New Member

Rick Rothstein

MrExcel MVP

tlinht

New Member

tlinht

New Member

Rick Rothstein

MrExcel MVP

tlinht

New Member

tlinht

New Member

Similar threads

Forum statistics

Share this page

Consecutive appearance

New Member

MrExcel MVP

Excel Facts

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock