# Find If

#### DCarrilho

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### portews

##### Active Member
Try this:

Code:
``````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

##### Well-known Member
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

##### Well-known Member
If you are comfortable with a helper column, then you can use this:

Unknown
ABC
10
213
323
40
5134
63034
7334
80
9163716
10143714
113373
124374
130
Sheet5
Cell Formulas
RangeFormula
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.

Last edited:

#### DCarrilho

##### New Member
Thank you all

All of them works

Happy to help

Replies
7
Views
446
Replies
2
Views
374
Replies
17
Views
933
Replies
16
Views
2K
Replies
40
Views
2K

1,191,184
Messages
5,985,175
Members
439,945
Latest member
ospe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back