# Find next whole number in list

#### Tetra201

##### MrExcel MVP
Yet another approach to finding offset between the first and second numbers:

=SUM(SMALL(IF(ISNUMBER(A2:A19),ROW(A2:A19)),{1,2})*{-1,1})

or

=SUM(SMALL(IF(B2:B19="Family",ROW(B2:B19)),{1,2})*{-1,1})

Please note: these are array formulas; they have to be entered using Ctrl+Shift+Enter, not just Enter.

P.S.: The formulas can be easily modified for finding offset between the first and ,say, third numbers -- if you ever need this. Just change the highlighted 2 to 3.

Last edited:

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### petertenthije

##### Board Regular
Column B
=iferror(If(\$A2/\$A2=1,row(),””),””)

Column C
=if(\$B2=””,””,\$B2-iferror(max(\$B\$1:\$B1),0))

#### alansidman

##### Well-known Member
Here is a vba solution to find all the whole numbers and list them in column C if your data is currently in column A
Code:
``````Option Explicit

Sub FindInteger()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If IsNumeric(Range("A" & i)) Then
If Range("A" & i) = Int(Range("A" & i)) Then
lr2 = Range("C" & Rows.Count).End(xlUp).Row + 1
Range("C" & lr2) = Range("A" & i)
End If
End If
Next i
End Sub``````

#### Tom.Jones

##### Board Regular
Yet another approach to finding offset between the first and second numbers:

=SUM(SMALL(IF(ISNUMBER(A2:A19),ROW(A2:A19)),{1,2})*{-1,1})

or

=SUM(SMALL(IF(B2:B19="Family",ROW(B2:B19)),{1,2})*{-1,1})

Please note: these are array formulas; they have to be entered using Ctrl+Shift+Enter, not just Enter.

P.S.: The formulas can be easily modified for finding offset between the first and ,say, third numbers -- if you ever need this. Just change the highlighted 2 to 3.

Tetra201,

Your excellent formula can be entered as normal formulas, just by pressing enter.

#### ManUBlueJay

##### Board Regular
As I said my range has 7000 lines. I know what I am looking for. (In this case 835) Named "MYSearch". When I find 835 or MySearch. I need to find the next integer in the list to create a drop down with the numbers in between. I Was using Offset(Match) to find 835, and was planning to use height in Offset to finish my list. This number would be how many rows are between 835 and the next integer.

#### Rick Rothstein

##### MrExcel MVP
Code:
``````Sub FindInteger()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If IsNumeric(Range("A" & i)) Then
If Range("A" & i) = Int(Range("A" & i)) Then
lr2 = Range("C" & Rows.Count).End(xlUp).Row + 1
Range("C" & lr2) = Range("A" & i)
End If
End If
Next i
End Sub``````
I am not sure the OP is actually after the numbers themselves as opposed to the distance from each other; however, with that said, the following one-liner will output the same list as your code above...
Code:
``````Sub FindIntegers()
Columns("A").SpecialCells(xlConstants, xlNumbers).Copy Range("C2")
End Sub``````

#### ManUBlueJay

##### Board Regular
I could use VBA, but I don't want a list. I want to be able to find a number called MySearch. Find the next integer after Mysearch, and tell me how many rows are between the 2.

#### ManUBlueJay

##### Board Regular
I could use VBA, but I don't want a list. I want to be able to find a number called MySearch. Find the next integer after Mysearch, and tell me how many rows are between the 2.

#### Eric W

##### MrExcel MVP
Have you tried the formulas from posts 9 and 10? They do exactly what you're asking.

#### salim hasan

##### Board Regular
To match first an 2nd int this formula (Ctrl+Shift+Enter) and drag down 2 rows
Code:
``=INDEX(\$A\$2:\$A\$15,SMALL(IF(ISNUMBER(\$A\$2:\$A\$15),ROW(\$A\$2:\$A\$15)-ROW(\$A\$2)+1),ROWS(\$A\$1:A1)))``
to match rows Bet ween this formula (Ctrl+Shift+Enter)
Code:
``=MATCH(INDEX(\$A\$2:\$A\$15,SMALL(IF(ISNUMBER(\$A\$2:\$A\$15),ROW(\$A\$2:\$A\$15)-ROW(\$A\$2)+1),2)),\$A\$1:\$A\$15,0)-+MATCH(INDEX(\$A\$2:\$A\$15,SMALL(IF(ISNUMBER(\$A\$2:\$A\$15),ROW(\$A\$2:\$A\$15)-ROW(\$A\$2)+1),1)),\$A\$1:\$A\$15,0)-1``
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Last edited:

1,089,602
Messages
5,409,231
Members
403,256
Latest member
Viq