Extracting the Max in Succession.

[Jborg]

Column C	Column D
1	1
1	1
5	2
4	3
6	4
6	4
6	4
6	4
4	5
4	5
4	5
4	5
4	5
6	6
5	7
5	7
5	7
5	7
6	8

<colgroup><col width="64" span="2" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>

Hi Guys,
I wonder if anyone can help me in this.

I have a range of values in Col C, which I need to find and extract the max in succession. In this case it is 4, since there are 5 4's in succession. In column D I have created a formula as follows: =SUM(C4<>C3,D3) as a helper column and dragged down. In this way I managed to create a formula based on the helper column to extract the value I want which is:
=INDEX($C$4:$C$22,MATCH(MAX(FREQUENCY(MATCH($D$4:$D$22,$D$4:$D$22,0),MATCH($D$4:$D$22,$D$4:$D$22,0))),FREQUENCY(MATCH($D$4:$D$22,$D$4:$D$22,0),MATCH($D$4:$D$22,$D$4:$D$22,0)),0)).

My question is: Is there a way as to how I could extract the max in succession in column C without the Helper column???

Thanks in advance to all of you.

 

[Peter_SSs]

What do you want to do if there are 2 or more different numbers that have equal "max in succession"?
eg
1
1
2
2
2
2
3
3
3
3
4
5
5
5
5
6

 

[Jborg]

Peter: Thanks for answering immediately.

To be honest I am not concerned with that since i will not have that in my Data.
Presumably, if that would be the case, the formula would give the first one.
Not concerned with that

 

[Peter_SSs]

A direct method using standard Excel functions is not coming to mind. Somebody else may well come up with one but in any case you may find this user-defined function is acceptable.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MaxInSuccession(r As Range) As Variant
  Dim lCount As Long, i As Long, maxcount As Long
  Dim vMaxVal As Variant
  
  With r
    If .Rows.Count > 1 And .Columns.Count > 1 Then
      MaxInSuccession = CVErr(xlErrRef)
    Else
      vMaxVal = .Cells(1).Value
      For i = 2 To .Cells.Count
        If .Cells(i).Value = .Cells(i - 1).Value Then
          lCount = lCount + 1
          If lCount > maxcount Then
            maxcount = lCount
            vMaxVal = .Cells(i).Value
          End If
        Else
          lCount = 0
        End If
      Next i
      MaxInSuccession = vMaxVal
    End If
  End With
End Function

Excel Workbook
	C	D	E
3
4	1		4
5	1
6	5
7	4
8	6
9	6
10	6
11	4
12	4
13	4
14	4
15	4
16	4
17	6
18	5
19	5
20	5
21	5
22	6
23
Succession

 

[Peter_SSs]

A simpler helper column method would be as follows. (The helper column can be hidden after populating it with the formulas.)

D3 is empty or houses a 0.
D4 copied down

Excel Workbook
	C	D	E
3
4	1	0	4
5	1	1
6	5	0
7	4	0
8	6	0
9	6	1
10	6	2
11	4	0
12	4	1
13	4	2
14	4	3
15	4	4
16	4	5
17	6	0
18	5	0
19	5	1
20	5	2
21	5	3
22	6	0
23
Succession (2)

 

[Jborg]

Peter: Thanks alot.

You have been of great help!!!!!

Thanks again

 

[apo]

Hi..

Here's another way..

Assumes headers in row 1.

Result will be put in cell B2.

Private Sub CommandButton1_Click()
    Dim cntval As Long, cnt As Long, x0, it, Z, myarea, j As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet1").Range("A1").CurrentRegion
        With CreateObject("scripting.dictionary")
            For Each it In Sheets("Sheet1").Columns(1).SpecialCells(2).Offset(1)
                x0 = .Item(it.Value)
            Next
            Z = .keys
        End With
        For j = LBound(Z) To UBound(Z) - 1
            .AutoFilter 1, Z(j)
            For Each myarea In .Columns(1).Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Areas
                If myarea.Count > cnt Then
                    cnt = myarea.Count
                    cntval = Z(j)
                End If
            Next myarea
        Next j
        .AutoFilter
    End With
    Cells(2, 2).Value = cntval
    Application.ScreenUpdating = True
End Sub

 

[Jborg]

apo: Thanks

 

[Rick Rothstein]

Here is a little more compact UDF (user defined function... same kind of function that Peter posted) for you to consider...

Function MaxRepeats(Rng As Range) As Variant
  Dim X As Long, Max As Long, Numbers() As String, Nums() As String
  Numbers = Split(Replace(Join(Application.Transpose(Evaluate("IF(" & Rng.Address & _
            "=" & Rng.Offset(1).Address & "," & Rng.Address & ",""0"")")), "X"), 0, " "))
  For X = 0 To UBound(Numbers)
    Nums = Split(Trim(Replace(Numbers(X), "X", " ")))
    If UBound(Nums) > Max Then
      Max = UBound(Nums)
      MaxRepeats = Nums(0)
    End If
  Next
End Function

Note: Like Peter's UDF, this one will also work no matter what the values are in the range passed into it, whether they be numbers or text (just in case you simplified your problem for us).

 

[Rick Rothstein]

Here is a little more compact UDF (user defined function... same kind of function that Peter posted) for you to consider...

Function MaxRepeats(Rng As Range) As Variant
  Dim X As Long, Max As Long, Numbers() As String, Nums() As String
  Numbers = Split(Replace(Join(Application.Transpose(Evaluate("IF(" & Rng.Address & _
            "=" & Rng.Offset(1).Address & "," & Rng.Address & ",""0"")")), "X"), 0, " "))
  For X = 0 To UBound(Numbers)
    Nums = Split(Trim(Replace(Numbers(X), "X", " ")))
    If UBound(Nums) > Max Then
      Max = UBound(Nums)
      MaxRepeats = Nums(0)
    End If
  Next
End Function

Note: Like Peter's UDF, this one will also work no matter what the values are in the range passed into it, whether they be numbers or text (just in case you simplified your problem for us).

DO NOT USE THE ABOVE FORMULA ... It has a flaw which I am looking into... I'll be back with a correction in a little bit.