Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Missing number from list (Excel 2003)

    I have a long list of numbers. They are in groups of 24. I need to find the 2 or 3 sequential numbers that are missing from the list. For instance, 2531is missing. Is there a function or easy way to do this?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Missing number from list (Excel 2003)

    In C1 you could enter:
    <pre>=Min(A:A)</pre>


    Then in C2 enter:
    <pre>=IF(MAX(A:A)>C1,1+C1,"No More")</pre>


    copy C2 as far as needed (if it says "No More" you are too far...)

    In D1 enter:
    <pre>=IF(COUNTIF(A:A,C1)=0,"Missing","")</pre>


    Copy D1 down the column as far as column C goes. it will display "Missing" for each one that is missing.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing number from list (Excel 2003)

    Very elegant, well done, Steve.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing number from list (Excel 2003)

    How about this formula, starting in B2 in your example and copied down to the end of your range...

    =IF(OR(A2=A1, A2=A1+1), "", "missing")

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing number from list (Excel 2003)

    This one I learned from Aladin and needs the Morefunc addin.

    Caveat, it doesn't take into account the grouping of 24 as I came up with 2531 and 2535

    In B1, enter =MIN(A1:A360)
    In B2, enter =MAX(A1:A360)

    In C1 array enter,

    =SUBSTITUTE(MCONCAT(IF(ISNA(MATCH(SETV(ROW(INDIREC T(B1&":"&B2))),A1:A360,0)),","&GETV(),"")),",","", 1)

Posting Permissions

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