Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Consecutive appearance

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Consecutive appearance

    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


  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Consecutive appearance

    Questions


    A
    B
    C
    1
    Values
    Values
    2
    1
    4
    3
    2
    1
    4
    1
    2
    5
    2
    1
    6
    4
    2
    7
    1
    1
    8
    2
    2
    9
    1
    6
    10
    2
    5
    11
    1
    1
    12
    2
    2
    13
    6
    4
    14
    1
    3
    15
    2
    3



    What is the expected result if data like A1:A15? 2 (sequence in A2:A5) or 3 (sequence in A7:A12)?

    Is possible a sequence like C1:C15 - first number = 4?

    M.

  3. #3
    New Member
    Join Date
    Aug 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Consecutive appearance

    Thanks for the reply. A1:A15 would be 3......A2:A5 would be 2........A7:A12 would be 3. And yes it would be similar to C1:C15 which would be 3.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Consecutive appearance

    See if this is Ok
    It's a specific solution - it works only with 2-digit sequences


    A
    B
    C
    1
    Values
    Max Consecutives 1-2
    2
    1
    3
    3
    2
    4
    1
    5
    2
    6
    1
    7
    2
    8
    1
    9
    4
    10
    1
    11
    2
    12
    1
    13
    4
    14
    1
    15
    6
    16
    1
    17
    2
    18



    Array formula in C2
    =MAX(FREQUENCY(IF((A2:A100=1)*(A3:A101=2)+(A2:A100=2)*(A1:A99=1),ROW(A2:A100)),IF(((A2:A100=1)*(A3:A101=2)+(A2:A100=2)*( A1:A99=1))=0,ROW(A2:A100))))/2

    confirmed with Ctrl+Shift+Enter

    Hope this helps

    M.

  5. #5
    New Member
    Join Date
    Aug 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Consecutive appearance

    Thank you very much, that worked perfectly. Now you mentioned it only works for 2 digit sequences, could text be substituted for the numbers?

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Consecutive appearance

    Quote Originally Posted by tlinht View Post
    Thank you very much, that worked perfectly. Now you mentioned it only works for 2 digit sequences, could text be substituted for the numbers?
    This?


    A
    B
    C
    1
    Text
    Max consecutives X-Y
    2
    Z
    4
    3
    X
    4
    Y
    5
    W
    6
    X
    7
    Y
    8
    X
    9
    Y
    10
    X
    11
    Y
    12
    X
    13
    Y
    14
    W
    15
    X
    16
    Y
    17
    R
    18
    Z
    19
    20


    Array formula in C2
    =MAX(FREQUENCY(IF((A2:A100="X")*(A3:A101="Y")+(A2:A100="Y")*(A1:A99="X"),ROW(A2:A100)),IF(((A2:A100="X")*(A3:A101="Y")+(A2:A100="Y")*(A1:A99="X"))=0,ROW(A2:A100))))/2

    Ctrl+Shift+Enter

    M.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Consecutive appearance

    Here is a mildly-tested UDF (user defined function) which should work for any number of consecutive numbers and/or words. The first argument for the function is the contiguous, vertical range of cells to be examined and, after that, you list, in order (delimited with commas), the values (text needs to be quoted, numbers do not) that make up your consecutive list. So, after installing the UDF, for the original problem, you would use this formula...

    =MaxConsecutives(A2:A15,1,2)

    One the other hand, if you wanted to find the maximum number of times three cells contained the words First, Second and Third (in that order), you would use this formula...

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

    Here is the UDF you need to install to make the above formulas work...

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

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use MaxConsecutives just like it was a built-in Excel function as shown in the formulas above.

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Last edited by Rick Rothstein; Aug 2nd, 2014 at 06:27 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Aug 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Consecutive appearance

    Right on the money, thanks again. One last question, I promise, how would that formula look with a sequence of 3. So instead of x,y.....x,y,z.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Consecutive appearance

    Quote Originally Posted by tlinht View Post
    Right on the money, thanks again. One last question, I promise, how would that formula look with a sequence of 3. So instead of x,y.....x,y,z.
    Try the UDF provided by Rick Rothstein.

    M.

  10. #10
    New Member
    Join Date
    Aug 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Consecutive appearance

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •