Find next whole number in list

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
302
Office Version
  1. 2016
Platform
  1. Windows
I have a long is of Id numbers, some of have a letter attatched some of whole numbers. Can any one help me find the second whole number in a list. "Id" is in A1. Through Match I can find 835 (I need the number of rows from A1, So I return 5. I need to find the number of rows 839 is from 835. Any help would be appreciated.

Id
834e
834f
834g
835
832f
473m
835c
835d
835e
835f
839
832g
346g
836c

<colgroup><col></colgroup><tbody>
</tbody>
 
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:
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Have you tried the formulas from posts 9 and 10? They do exactly what you're asking.
 
Upvote 0
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


Book1
ABCDEFG
2834e1st Int835rows between6
3834f2nd In839
4834g
5835
6832f
7473m
8835c
9835d
10835e
11835f
12839
13832g
14346g
15836c
Sheet1
Cell Formulas
RangeFormula
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)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top