SEQUENCE and missing data

[sal21]

Wath is the item missing value in this sequence...


note:
the sequence id letter and a numeric value from 000 to 1000

for example missing value are A002, A003 in row 2 to 5

[HansV]

Code: Select all

Sub ListMissing()
    Dim v As Variant
    Dim m As Long
    Dim r As Long
    Dim s As Long
    Dim v1 As String
    Dim v2 As String
    Dim i As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:B" & m).Value
    s = 1
    v1 = v(2, 1)
    For r = 3 To m
        v2 = v(r, 1)
        If Left(v2, 1) = Left(v1, 1) Then
            For i = Val(Mid(v1, 2)) + 1 To Val(Mid(v2, 2)) - 1
                s = s + 1
                v(s, 2) = Left(v1, 1) & Format(i, "000")
            Next i
        End If
        v1 = v2
    Next r
    Range("A1:B" & m).Value = v
    Application.ScreenUpdating = True
End Sub

[p45cal]

In the attached, a table at cell D1.
Change the data in the table Table1 at cell A1, then right-click the result table and choose Refresh to update it.
It will work with more than single letter prefixes, even in the same list.

[p45cal]

sal21, did you want all the missing values from 000 to 1000 or just between the min and the max for each letter? I included all values (although I started at 001, not at 000 but that's easily tweaked.

HansV, I tried yours and it largely agreed; what happens if there are more missing than are present? as in:
A001
A040
B005
B020

[HansV]

My code doesn't anticipate that. The kind of data that Sal21 processes won't have more missing data than present data (I've been helping him for 17 years or so now.) But if you look at it from a theoretical viewpoint, my code is not ideal.

[p45cal]

Good to know. In that case (17yrs) do you happen to know the answers to my questions to sal21?

I did another variation (cell F1) which puts the missing numbers next to where they belong… the attached contains untidy temporary queries used in development until I get sal21's answers/opinions.

[HansV]

I think he wanted missing numbers between min and max for each letter.