indicate a missing number in a sequence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Prior to sending this post, I have read many posts and have not quite found
the answer I am looking for. I hope someone can help me with a formula.

I need to track tickets used. More importantly, I need to know if a ticket
in a sequence is missing. I will be tracking start & stop numbers and
tickets used. I'm looking for an indicator of some sort to tell me when a
ticket is missing or out of sequence.
Example

A1=1 B1=11 C1=10
A2=12 B2=14 C2=2
A3=17 B3=20 C3=3 (FLAG the 2 missing tickets 15 & 16)

Any assistance would be apprciated.
thanks,
Mark
 
You cannot flag the ones that are missing! I was going to suggest flagging
all those around the missing using conditional formatting and this formula

=NOT(AND(OR(A1=MAX($A$1:$C$3),COUNTIF($A$1:$C$3,A1+1)>0),OR(A1=MIN($A$1:$C$3
),COUNTIF($A$1:$C$3,A1-1)>0))(

but it shows more than 14 and 17 as lots more are missing.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Hi,
Try this which lists missing values in column D. "UsedRange" is
assumed to be Columns A to C with data starting in row 2 (row 1 is header):

Sub CheckSequence(ByVal rng As Range)

Dim v As Variant
Dim r As Long, c As Integer
MsgBox rng.Address
v = rng
For r = 3 To UBound(v, 1) <=== change 3 to 2 if no header row

If v(r, 1) <> v(r - 1, 2) + 1 Then
Stra = ""
For i = v(r - 1, 2) + 1 To v(r, 1) - 1
Stra = Stra + Trim(Str(i)) & ","
Next i
Stra = Left(Stra, Len(Stra) - 1)
rng(r, 4) = Stra
End If
Next r


Sub mytest()
CheckSequence ActiveSheet.UsedRange '<=== Change range if required
End Sub
 
For no missing data A9 should equal B8 plus one:


In D1 put:
=IF(A2=B1+1,"","ERROR") and copy down the column. This won't tell you
what's missing, but it will tell you that something is missing.
 
HI Gary:

I tried your suggestion and found that it errored for everything. Using the
example I listed, it gives me an error even though there are no missing
numbers (or tickets)
M.
 
Hi Toppers:

Maybe I'm reading too much into this but your answer is appearing too
technical for me. Can you make it more user friendly?
thanks,
M.
 
Maybe to clarify more. On same days I will use or sell more than one ticket.
Line B1 - A1 = C1 (or 10 tickets sold). Is the +1 used only for when it is
1 above the previous number?
Thanks,
Mark
 
I tried Gary's solution with your data as posted and correctly indicated an
"error" i.e. data missing (15,16) in row 3.
 
Assuming you are happy with Gary's solution, try this modified version which
gives range of missing tickets i.e. 15-16 in your example in row 3:

=IF(A2=B1+1,"", B1+1 & "-" & A2-1)
 
I am happy with the formula and trying to understand it, it does make sence
but I cannot seemt o get it to work for me?
 
I GOT IT!!

I was using the worng cell numbers!! Both Gary's original version and the
MODIFIED version is PERFECT.

Thank you all for you assistance!
Mark
 
Thank you for your help with this. I would like to add one twist. Is there
a way to limit the number to 500? The tickets come in booklets of 500 and I
do not need the formula to go higher than this.

Thanks again.
 
Back
Top