indicate a missing number in a sequence

[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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

I tried Gary's solution with your data as posted and correctly indicated an
"error" i.e. data missing (15,16) in row 3.

 

[Guest]

Shouldn't that be 11 tickets sold (numbers to 1 to 11 inclusive)?

 

[Guest]

You are correct......thank you again, I would have another ticket missing!!

 

[Guest]

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)

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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.