algorithm for checking receipt number

[yky]

I need to check whether receipt numbers are consecutive and if not, what numbers are missing. I use a receipt book which has 80 receipt in it. Thus, the number runs from one to 80. In any given day, I could have receipts whose number ranges from, say, 15 to 55, or 73 through 80 to 23.

It is very easy for a human being to find out the first, last, and any missing receipt numbers. But how do I put it into a algorithm? I have been thinking about this for few days and still do not know where to start.

 

[T. Valko]

I need to check whether receipt numbers are consecutive and if not, what numbers are missing. I use a receipt book which has 80 receipt in it. Thus, the number runs from one to 80. In any given day, I could have receipts whose number ranges from, say, 15 to 55, or 73 through 80 to 23.

It is very easy for a human being to find out the first, last, and any missing receipt numbers. But how do I put it into a algorithm? I have been thinking about this for few days and still do not know where to start.

Are you wanting a VBA procedure or a formula?

What version of Excel are you using?

 

[yky]

Are you wanting a VBA procedure or a formula?

What version of Excel are you using?

Thanks for the reply.

I forgot all about the basic info. I'm looking for a VBA procedure. I use Excel 2010 on Windows 7.

 

[Armando Montes]

Perhaps

Sub test()
'http://www.ozgrid.com/forum/showthread.php?t=156758
    Dim i As Long, x, r As Range
    For i = Range("b" & Rows.Count).End(xlUp).Row To 2 Step -1
        x = Cells(i, "b") - Cells(i - 1, "b")
        If x > 1 Then
            Rows(i).Resize(x - 1).Insert
            Cells(i - 1, "b").AutoFill Cells(i - 1, "b").Resize(x), 2
        End If
    Next
End Sub

 

[Ron Coderre]

If a regular formula is ok to use...
This one lists the missing numbers between the min and max values in Col_A

B1: =IFERROR(SMALL(INDEX(ISNA(MATCH(ROW(INDEX(A:A,MIN(A:A)):
INDEX(A:A,MAX(A:A))),A:$A,0))*ROW(INDEX(A:A,MIN(A:A)):INDEX(A:A,
MAX(A:A))),0),COUNT(A:A)+ROWS($1:1)),"")

Copy that formula down as far as you need.

 

[yky]

Perhaps

Sub test()
'http://www.ozgrid.com/forum/showthread.php?t=156758
    Dim i As Long, x, r As Range
    For i = Range("b" & Rows.Count).End(xlUp).Row To 2 Step -1
        x = Cells(i, "b") - Cells(i - 1, "b")
        If x > 1 Then
            Rows(i).Resize(x - 1).Insert
            Cells(i - 1, "b").AutoFill Cells(i - 1, "b").Resize(x), 2
        End If
    Next
End Sub

Thanks for the code.

Even though I cannot use the code directly, your code gives me some idea. I can declare a dynamic array, run through the receipt numbers and count the number of missing receipt, set the dimension of the dynamic array, run through the receipt numbers again and populate the array with missing number.

There is one hurdle. I may have receipt numbers like the following:

77
78
79
80
01
02
03

or even worse, when 80 is missing,

77
78
79
01
02
03

How would I handle these cases? Sorting the numbers doesn't seem to help.

 

[yky]

If a regular formula is ok to use...
This one lists the missing numbers between the min and max values in Col_A

B1: =IFERROR(SMALL(INDEX(ISNA(MATCH(ROW(INDEX(A:A,MIN(A:A)):
INDEX(A:A,MAX(A:A))),A:$A,0))*ROW(INDEX(A:A,MIN(A:A)):INDEX(A:A,
MAX(A:A))),0),COUNT(A:A)+ROWS($1:1)),"")

Copy that formula down as far as you need.

Thanks. Even though I am looking for a VBA solution, I learn something from the formula.