Out of sequence

[slomof]

Hi,

Ive got a huge list of numbers with letters example:

C144846AJB

C144847AJB

C144848AJB

C144849AJB

C144869DC

C144872EG

C144876GH

C144877AK

<colgroup><col></colgroup><tbody>
</tbody>

how do pick up quickly what is out of sequence?

 

[jproffer]

....why not....

What do you mean by out of sequence? Your example is in sequence, as far as Excel is concerned.

 

[slomof]

What I mean if there is numbers missing example: how can mark that between C144869DC and C144872EG there are some missing

 

[footoo]

Assuming your data starts in A2 and column C is available to list the missing numbers :

Sub MG17Jan41()
Dim Rng As Range, Dn As Range, n As Long, Dic As Object
Dim oMax As Long, oMin As Long, c As Long, Tem As String
c = 1
Set Rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng: Dic(Mid(Dn.Value, 2, 6) * 1) = 0: Next
oMax = Application.Max(Dic.keys)
oMin = Application.Min(Dic.keys)
Tem = Range("C1").Value: Range("C:C").ClearContents: Range("C1").Value = Tem
For n = oMin To oMax
    If Not Dic.exists(n) Then
        c = c + 1
        Cells(c, "C") = n
    End If
Next n
End Sub

Above code adapted from here : https://www.mrexcel.com/forum/excel-questions/914909-vba-require-find-missing-numbers.html