Find If

[DCarrilho]

Hi guys

I have a column (a big column) with a sequence of numbers separated by zeros. I want to know how i can copy to another column the sequence of numbers (separated by zeros) that is SUM is the highest.
I give you an example below:

A	B
0
1
2
0
1
2
3
0
1	1
2	2
3	3
4	4
0

<tbody>
</tbody>

Thank you guys

 

[portews]

Try this:

Sub GreatestSum()
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/981379-find-if.html[/COLOR]
Dim i As Integer
Dim x As Long
Dim startcell As Integer
[COLOR=#00ff00]'dim greatest as Long in case decimals are used[/COLOR]
Dim greatest As Long
Dim GreatPos As Integer


[COLOR=#00ff00]'for each row starting with 1 until the last one with a value[/COLOR]
For i = 1 To ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
[COLOR=#00ff00]    'check to see if you are starting an new sequence at zero[/COLOR]
    If Cells(i, 1) = 0 Then
[COLOR=#00ff00]        'if the last sequence was greater than the greatest sequence so far[/COLOR]
        If x > greatest Then
[COLOR=#00ff00]            'set the sum to the greatest[/COLOR]
            greatest = x
[COLOR=#00ff00]            'mark the position of the zero that started it.[/COLOR]
            GreatPos = startcell
        End If
[COLOR=#00ff00]        'mark the postion of the latest zero[/COLOR]
        startcell = i
[COLOR=#00ff00]        'reset the sum to zero[/COLOR]
        x = 0
    End If
[COLOR=#00ff00]    'start summing this sequence[/COLOR]
    x = x + Cells(i, 1)
Next
[COLOR=#00ff00]'reset the counter[/COLOR]
i = 1
[COLOR=#00ff00]'loop thru the greatest sequence[/COLOR]
Do
[COLOR=#00ff00]    'B column = A column, starting at the zero position[/COLOR]
    Cells(GreatPos + i, 2) = Cells(GreatPos + i, 1)
    'next row
    i = i + 1
[COLOR=#00ff00]'stop looping when you hit the next zero[/COLOR]
Loop Until Cells(GreatPos + i, 1) = 0
End Sub

 

[FDibbins]

Perhaps this?

	A​	B​
1​	A	B
2​	0
3​	1
4​	2
5​	0
6​	1
7​	2
8​	3
9​	0	0​
10​	1	1​
11​	2	2​
12​	3	3​
13​	4	4​
14​	0

B2=IF(A2=MAX(A:A),A2,IF(OR(B3=0,B3=""),"",IF(A2>=0,A2,"")))
copied down

 

[Nishant94]

If you are comfortable with a helper column, then you can use this:

Unknown
	A	B	C
1	0
2	1	3
3	2	3
4	0
5	1	34
6	30	34
7	3	34
8	0
9	16	37	16
10	14	37	14
11	3	37	3
12	4	37	4
13	0
Sheet5

Cell Formulas
Range	Formula
C1	=IF(MAX($B$1:$B$13)=B1,A1,"")
B1	{=IF(A1=0,"",SUM(INDEX($A$1:$A$13,LOOKUP(ROW(A1),IF($A$1:$A$13=0,ROW($A$2:$A$14)))):INDEX($A1:$A$13,MATCH(TRUE,IF(IF($A1:$A$13=0,ROW($A1:$A$13)),TRUE,FALSE),0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DCarrilho]

Thank you all

All of them works

 

[FDibbins]

Happy to help