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.

 

[Rick Rothstein]

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.

I found the problem (stemmed from a last minute change that I forgot to carry through to one other location in the formula). Anyway, the following UDF works correctly (well, at least it produces the identical output that Peter's UDF produces, so I conclude it works correctly:wink. By the way, in timed tests for both this UDF and Peter's, they both performed identically taking, on average, 0.03 seconds (on my somewhat quick computer) to process a 10,000-cell range.

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

 

[apo]

Hi Rick..

I wouldn't mind testing how fast (or slow) mine is too.. I doubt it is as fast as yours or Peters because I use a couple of loops.

By the way, in timed tests for both this UDF and Peter's, they both performed identically taking, on average, 0.03 seconds (on my somewhat quick computer) to process a 10,000-cell range.

To test.. did you repeat the same numbers that the OP shows in Column C of his first post to fill the 10 000 cell range or did you use a larger set of different numbers..?

 

[Peter_SSs]

Hi Rick..

I wouldn't mind testing how fast (or slow) mine is too.. I doubt it is as fast as yours or Peters because I use a couple of loops.

To test.. did you repeat the same numbers that the OP shows in Column C of his first post to fill the 10 000 cell range or did you use a larger set of different numbers..?

The speed of your code depends more on the number of different values that can occur in the column than Rick's or mine does.

My initial test was done with 10,000 randomly generated single digit numbers (1-9). Approximate timing in seconds on my machine (considerably slower than Rick's by the look of it) was
apo 1.5
Peter 0.08
Rick 0.06

However, if there was such a large range to deal with and/or speed was an issue, I would use the function below which, on the same data, took only 0.01 seconds.

Note another difference between your code and Rick's & mine is that ours are functions, so will update if the data changes whereas your code would need to be run again.

I did another test with 10,000 rows of randomly generated digits but only digits 1, 2 and 3 allowed. Timing for Rick & me was pretty much unchanged, your code time dropped to 0.6 secs (only 3 AutoFilters to do)

Function MaxInARow(r As Range) As Variant
  Dim a
  Dim lCount As Long, i As Long, maxcount As Long, rws As Long
  Dim vMaxVal As Variant
  
  a = r.Value
  rws = UBound(a, 1)
  vMaxVal = a(1, 1)
  For i = 2 To rws
    If a(i, 1) = a(i - 1, 1) Then
      lCount = lCount + 1
      If lCount > maxcount Then
        maxcount = lCount
        vMaxVal = a(i, 1)
      End If
    Else
      lCount = 0
    End If
  Next i
  MaxInARow = vMaxVal
End Function

 

[apo]

Hi Peter..

Thanks for the detailed reply.

I can see how you have made your code much faster by putting the values in an array and 'doing the job' in memory..

I haven't made many UDF's.. something I plan on tackling soon..

 

[Joyner]

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)

I know it doesn't matter for the solution, but I still wanted to see the actual count of the repeated numbers, so fro the formula in D4. I would do:

=MAX(1,(D3+1)*(C4=C3))

 

[Jborg]

Joyner: Thanks for sharing, Just for the sake of asking......would you have a solution in a formula to do it without a helper column??? cause i didn't manage.

 

[Peter_SSs]

.. I still wanted to see the actual count of the repeated numbers, so fro the formula in D4. I would do:

=MAX(1,(D3+1)*(C4=C3))

In that case I wouldn't bother invoking an extra function, but simply ..

=D3*(C4=C3)+1

 

[Joyner]

Peter,

I thought it was the one with the most Functions wins...


As always, thanks for the better alternate formula.