Highest consecutive values in column

[psctornado]

Hi All,

I am looking to create a formula where it will return the 30 consecutive values in column B.

As an example :

Column A Column B Values A2 2 A3 4 A4 5 A5 5 A6 6 A7 6 A8 6 A9 7 A10 758 A11 8 A12 9 A13 15 A14 55 A15 35 A16 98 A17 44 A18 15 A19 11 A20 78 A21 432 A22 290 A23 8 A24 9 A25 15 A26 55 A27 59 A28 73 A29 88 A30 103 A31 117 A32 132 A33 147 A34 161 A35 176 A36 191 A37 206 A38 220 A39 235 A40 250 A41 264 A42 279 A43 294 A44 308 A45 323 A46 338 A47 353 A48 367 A49 382 A50 397 A51 411 A52 426 A53 441 A54 456 A55 470 A56 485 A57 500 A58 514 A59 529 A60 544 A61 558 A62 573 A63 588 A64 603 A65 617 A66 632 A67 647 A68 661 A69 676 A70 691 A71 705 A72 720 A73 735 A74 750 A75 764 A76 779 A77 794 A78 808 A79 823 A80 0 A81 0 A82 1 A83 2 A84 3 A85 4 A86 5 A87 941 A88 955 A89 970 A90 985 A91 999 A92 1014 A93 1029 A94 1044 A95 1058 A96 1073 A97 1088 A98 1102 A99 1117 A100 1132 A101 50

I would expect from the above, that values A65 through A100 are the largest consecutive string & I would like to return each value from the string in a separate column, say column C. Is there a formula in which I can do that?

Any help would be appreciated!

[Aussiebear]

Not sure I follow your logic here as to "consecutive" string given that if cell A65 starts at 617 and then the values increase either by 14 or 15 until cell A79 which then drops to 0. Cell A80 to A86 either may or may not increase by 1, the Cell A87 restarts the potential increase of either 14 or 15 until cell A100.

[Paul_Hossler]

A65 617 A66 632 A67 647 A68 661 A69 676 A70 691 A71 705 A72 720 A73 735 A74 750 A75 764 A76 779 A77 794 A78 808 A79 823 A80 0 A81 0 A82 1 A83 2 A84 3 A85 4 A86 5 A87 941 A88 955 A89 970 A90 985 A91 999 A92 1014 A93 1029 A94 1044 A95 1058 A96 1073 A97 1088 A98 1102 A99 1117 A100 1132

I would expect from the above, that values A65 through A100 are the largest consecutive string & I would like to return each value from the string in a separate column, say column C. Is there a formula in which I can do that?

How exactly are you defining 'consecutive' since I can't see anything consecutive there

[psctornado]

Sorry I should clarify then. When I say consecutive, it would be the highest consecutive sum of 30 values (ie rows 65 through 100, their total for those values are the highest, vs other consecutive groups of 30 numbers).

[Aussiebear]

Surely having 7 lowest values within that range you nominated simply debunks your suggestion of "Highest consecutive sum of values"

[psctornado]

Surely having 7 lowest values within that range you nominated simply debunks your suggestion of "Highest consecutive sum of values"

I'm not sure how it 'debunks' my statement. If we sum up the consecutive values in that string, it sums to the highest value out of the 100 available. if I summed from rows 64 through 99, the sum total would be less, etc. I'm not sure how pointing out that the 7 lowest values in that string gets any closer to solving the question I had asked originally. I can resolve this thread if there isn't a solution to my question. I understand there may be not be a solution to every question to excel.

[Aussiebear]

I see now where I was wrong. From Post#2 I was looking for a pattern of values that changed either by "14 or 15", hence the 7 lowest values had thrown me off what you were chasing.

[Paul_Hossler]

If I'm understanding

This is only the first part. The 30 values would need to be pulled out separately is my logic is right

Capture.JPG

[psctornado]

This looks great! Is there were to extract the values from rows 71 to 100 then?

If I'm understanding

This is only the first part. The 30 values would need to be pulled out separately is my logic is right

Capture.JPG

[Paul_Hossler]

Capture.JPG

This is with worksheet formulas. I could be done with a macro or a user defined function

[psctornado]

Excellent. Thanks for the help!

[Paul_Hossler]

Here's another way that uses a VBA User Defined Function (UDF) to find the group of N (a user input) entries and return a dynamic array to the worksheet for the user input number of entries to test.

You could do a classical ctrl-shift-enter array entry also, but I think this is easier since you do not need to re-select the output range each time you change the number of values to check

The green F2 is the start of the dynamic array and the orange F1 is the Sum of the dynamic array's spill range



Capture.JPG

Option Explicit


Dim aryNumbers As Variant


Function LargestConseq(ValuesIn As Range, Optional HowMany As Long = 30) As Variant
    Const MinHowMany As Long = 10
        
    Dim rNumbers As Range
    Dim idxEntry As Long, idxHigh As Long
    Dim totHigh As Double, totCurrent As Double
    Dim aryOut() As Double
    
    On Error GoTo ErrHandler
    
    'error conditions
    If HowMany < MinHowMany Then Err.Raise 1000, "LargestConseq", "HowMany too small (" & HowMany & "). Must be at least " & MinHowMany
    If ValuesIn.Columns.Count <> 1 Then Err.Raise 1002, "LargestConseq", "Input Range must be one column"




    'determine 'real' data range, if entire col entered or first entry is non-numeric
    If ValuesIn.Rows.Count = ValuesIn.Parent.Rows.Count Then
        Set rNumbers = Range(ValuesIn.Cells(1, 1), ValuesIn.Cells(ValuesIn.Parent.Rows.Count, 1).End(xlUp))
    End If
    
    If Not IsNumeric(rNumbers.Cells(1, 1).Value) Then
        Set rNumbers = rNumbers.Cells(2, 1).Resize(rNumbers.Rows.Count - 1, 1)
    End If
    
    If rNumbers.Rows.Count < HowMany Then Err.Raise 1001, "LargestConseq", "Not enough cells in Input Range (" & rNumbers.Rows.Count & ") for requested number (" & HowMany & ")"
    
    'bring data into array
    aryNumbers = Application.WorksheetFunction.Transpose(rNumbers)
    


    'prepare output array
    ReDim aryOut(1 To HowMany)


    'loop data, sum HowMany enteries, keep track of largest total and it's index
    For idxEntry = HowMany To UBound(aryNumbers)
        totCurrent = pvtSum(idxEntry, HowMany)
        If totCurrent > totHigh Then
            totHigh = totCurrent
            idxHigh = idxEntry
        End If
    Next idxEntry


    'move largest block of HowMany to output array
    For idxEntry = LBound(aryOut) To UBound(aryOut)
        aryOut(idxEntry) = aryNumbers(idxHigh - HowMany + idxEntry)
    Next idxEntry


    'return output array and exit
    LargestConseq = Application.WorksheetFunction.Transpose(aryOut)
    
    Exit Function


ErrHandler:
    With Err
        LargestConseq = .Description
    End With


End Function


Private Function pvtSum(idxStart As Long, cntHowMany As Long) As Double
    Dim X As Double
    Dim i As Long
    
    For i = cntHowMany - 1 To 0 Step -1
        X = X + aryNumbers(idxStart - i)
    Next i
    
    pvtSum = X
End Function

[psctornado]

Wow this is even better! I greatly appreciate your help! Thanks again!!!

[Paul_Hossler]

I'm glad

My very personal preference is to use UDFs for such complicated tasks instead of helper columns and filling down formulas, etc.

Play with it and let me know if you run into any problems