Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing sequence numbers (Excel 2000/XP)

    I have a spreadsheet that has invoice numbers in column A. They run in numerical order but there are some missing. Is there any way to have Excel look at this and tell me where the sequence breaks at each missing number?
    Thanks in advance

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Missing sequence numbers (Excel 2000/XP)

    A very simple approach would be to sort the invoices in ascending order, insert a new column B, and in column B, second row of invoice numbers (I'll assume it's row 3) enter

    =IF(A3<>A2+1,"SEQUENCE BREAK","")

    copy it down and look for the break messages. There may be more complex array formula approaches to count the number of breaks, I'll look around.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Missing sequence numbers (Excel 2000/XP)

    How about the following, where my list is in A6 to A10 (adjust accordingly for yours)

    { =SUM(IF(A6:A10<>A5:A9+1,1,0))-1 }

    as an array formula entered in, say, cell B6.

    You type the formula starting from = but don't enter the braces. Using CTRL+SHIFT+enter tells Excel this is an Array formula and it puts in the braces in the formula bar.

    A few words about above:
    1. list can't start in row 1 (assuming it's vertical) since the right side of the comparison looks at the cell before the first cell in the list.
    2. The -1 at the end is based on the assumption that the first cell (A6 in my case) won't compare to the cell above it so I have to adjust for that.

    Everything I know about Array Formulas is due to Bob U.

    Fred

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Missing sequence numbers (Excel 2000/XP)

    My previous post would just give you a count of the gaps. If there are none, the count is 0 of course.

    If you want to have the adjacent cell say whether there's a gap between the cell to the left and the one above, you can use what John suggested or you can use the array formula

    { =IF(A7:A10<>A6:A9+1,"gap","no gap") }
    CTRL+SHIFT+entered with A7:A10 selected

    Here I purposely start with the second cell in the list to avoid the problem of the first cell mentioned in the previous post.

    This is pretty much equivalent to John's approach.

    With either of these, you could then get a count of the gaps, using my array formula from earlier or something like
    =countif(B7:B10,"gap")

    You might even want to do a conditional format on the entries in Col B to shade the cells with the word "gap" so they stand out.


    Fred

Posting Permissions

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