# Thread: Find next whole number in list Thanks: 0 Likes:  2 Post #5340513 (1)Post #5340680 (1)

1. ## Re: Find next whole number in list

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.

2. ## Re: Find next whole number in list

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

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

3. ## Re: Find next whole number in list

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

4. ## Re: Find next whole number in list

Originally Posted by Tetra201
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.

5. ## Re: Find next whole number in list

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.

6. ## Re: Find next whole number in list

Originally Posted by alansidman
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```

7. ## Re: Find next whole number in list

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.

8. ## Re: Find next whole number in list

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.

9. ## Re: Find next whole number in list

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

10. ## Re: Find next whole number in list

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`
ABCDEFG
2834e1st Int835rows between6
3834f2nd In839
4834g
5835
6832f
7473m
8835c
9835d
10835e
11835f
12839
13832g
14346g
15836c

Sheet1

Array Formulas
CellFormula
G2{=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}
D2{=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)))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•