Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Find next whole number in list

  1. #11
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,394
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Last edited by Tetra201; Sep 11th, 2019 at 09:03 PM.

  2. #12
    Board Regular
    Join Date
    Sep 2012
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #13
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,944
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  4. #14
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find next whole number in list

    Quote Originally Posted by Tetra201 View Post
    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. #15
    Board Regular
    Join Date
    Aug 2012
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #16
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Find next whole number in list

    Quote Originally Posted by alansidman View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #17
    Board Regular
    Join Date
    Aug 2012
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #18
    Board Regular
    Join Date
    Aug 2012
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #19
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find next whole number in list

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

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #20
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Last edited by salim hasan; Sep 12th, 2019 at 04:37 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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