Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Find next whole number in list

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

    Default Find next whole number in list

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find next whole number in list

    How about
    =MATCH(AGGREGATE(15,6,A2:A15,2),A2:A15,0)-MATCH(AGGREGATE(15,6,A2:A15,1),A2:A15,0)-1
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    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 have never used aggregate. The list I have in real terms is 7000 lines long. The first whole number (835) I am looking for could be anywhere in that list, but I can find it using match as I know the number. I need to find the next whole number (839) after 835 so I can count the rows in between.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find next whole number in list

    Did you try my suggestion?

    Also will 835 always be the first number in the list?
    and will 839 always be the 2nd number in the list?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    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 couldn't quite figure it out as Aggregate is new to me.
    No the numbers are variable. I do know the first number I am looking for. I am trying to make a list of the numbers in between the first number and the next number after that one

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find next whole number in list

    If it's always the first two numbers, then just use the formula I provided & change the range "A2:A15" to suit your actual data.
    Last edited by Fluff; Sep 11th, 2019 at 11:10 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    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

    It is not the first 2 numbers. I can find the first using match in my range of 7000 cells. It could be anywhere in there. The next number is what I would like to find in relation to the first.
    Another option may be the next cell over has the word "Family" in every cell that has a whole number, and the word "name" in all the ones that have a letter. It may be easier to find the correct family but my brain is stumped

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find next whole number in list

    If you don't want the first two numbers why did you say
    Can any one help me find the second whole number in a list.
    This is now beyond my ability with formulae. Hopefully another member will step in & help.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    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

    Maybe:

    ABCD
    1IdFirst numberOffset to next number
    2834ename8357
    3834fname
    4834gname
    5835Family
    6832fname
    7473mname
    8835cname
    9835dname
    10835ename
    11835fname
    12839Family
    13832gname
    14346gname
    15836cname
    16921aname
    17922bname
    18888Family
    19923cname

    Sheet4



    Worksheet Formulas
    CellFormula
    D2=AGGREGATE(15,6,EXP(LN((ROW(B2:B20)-ROW(B2)+1)/(B2:B20="family")-MATCH(C2,A2:A20,0))),1)

    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. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,270
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Find next whole number in list

    Maybe...


    A
    B
    C
    D
    1
    Id
    First number
    Offset to next number
    2
    834e
    835
    7
    3
    834f
    4
    834g
    5
    835
    6
    832f
    7
    473m
    8
    835c
    9
    835d
    10
    835e
    11
    835f
    12
    839
    13
    832g
    14
    346g
    15
    836c
    16
    921a
    17
    922b
    18
    888
    19
    923c


    Formula in D2
    =IFERROR(MATCH(1,INDEX(--ISNUMBER(INDEX(A:A,MATCH(C2,A:A,0)+1):A$10000),),0),"Not found")

    M.

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
  •