slomof

New Member
Joined
Jun 19, 2018
Messages
2
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
....why not....

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

slomof

New Member
Joined
Jun 19, 2018
Messages
2
What I mean if there is numbers missing example: how can mark that between C144869DC and C144872EG there are some missing
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Assuming your data starts in A2 and column C is available to list the missing numbers :
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top