Check for missing invoice numbers

[luxboy]

Hi All,

Firstly thanks for taking the time to look at this post.

The problem I have is relating to invoices. I have an Excel spreadshee
which I write down all payments I make for invoices. The invoice number
are always a number and started at 1 and then increment by 1. IE 1, 2,
etc.

What I would like to do is to have a way in which I could look in m
sheet to see if I have any invoices missing. ie 6 and 8 is there but
is not.

Due to the nature of invoices they are not always in order so I migh
pay 9 before I pay 8 and therefore on my spreadsheet they wont be i
order. they are currently in order of when I paid them.

Would anyone know a way I could do this?

Many tnaks agai

 

[Bob Phillips]

Could you not just have a list of functions say starting in M1

=IF(ISNUMBER(MATCH(ROW(M1,$C$!:$C$1000,0))),"",ROW(M1)&" is missing")

and just copy down. Not particularly sophisticated, but should work.

 

[keepITcool]

could do it like following:

i'm sure there must be faster ways,(with sorting etc)
but this was nice and easy

copy/paste the code to a normal module.
select the range with the invoices
and fire it up..

and emm... it will check for non unique numbers too!

Sub CheckNumbers()
'needs a reference to "Microsoft Scripting Runtime"
'set via VBE Tools/References
Dim dicNrs As Scripting.Dictionary
Dim dicMis As Scripting.Dictionary
Dim dicDbl As Scripting.Dictionary
Dim rng As Range
Dim n&, nMin&, nMax&

Set dicNrs = New Dictionary
Set dicDbl = New Dictionary
Set dicMis = New Dictionary

For Each rng In ActiveWindow.RangeSelection.Cells
If Not IsEmpty(rng) Then
If dicNrs.exists(rng.Value) Then
dicDbl.Add rng.Address(0, 0), rng.Value
Else
dicNrs.Add rng.Value, Null
End If
End If
Next
nMin = Application.Min(dicNrs.Keys)
nMax = Application.Max(dicNrs.Keys)

For n = nMin To nMax
If Not dicNrs.exists(n) Then dicMis.Add n, Null
Next

If dicMis.Count = 0 Then
MsgBox "no missing"
Else
Set rng = Application.InputBox( _
dicMis.Count & " numbers missing" & vbLf & _
"select a vertical range to show them.", Type:=8)
rng.Cells(1) = "missing numbers"
rng.Cells(2).Resize(dicMis.Count) = Application.Transpose( _
dicMis.Keys)
End If

If dicDbl.Count > 0 Then
Set rng = Application.InputBox( _
dicDbl.Count & " numbers double" & vbLf & _
"select a vertical range to show them.", Type:=8)
rng.Cells(1) = "double Numbers"
rng.Cells(2, _
1).Resize(dicDbl.Count) = Application.Transpose(dicDbl.Keys)
rng.Cells(2, _
2).Resize(dicDbl.Count) = Application.Transpose( _
dicDbl.items)
End If

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


luxboy wrote :

 

[luxboy]

Perfect!


Works like a charm

Many thanks indee