Code To List Numbers Missing From Sequence

[Dazzawm]

I have a very long list of numbers in column A and I would like a code to tell me which numbers are missing from the sequence. Below is an example.

Excel 2010

	A
SS9822
SS9824
SS9826
SS9828
SS9830
SS9831
SS9833

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

[TD="align: center"]4570[/TD]

[TD="align: center"]4571[/TD]

</tbody>

​

So from above the following numbers are missing so I would like a list of these made in column D.

Excel 2010

	D
SS9823
SS9825
SS9827
SS9829
SS9832

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

</tbody>

​

Thanks

 

[gravanoc]

Hi Dazzawm, please let me know if I am understanding this correctly. Is that format that you are using there representative of what will be the common pattern throughout the long list of numbers? Also, are the numbers separate or joined? I'm seeing two columns, but I'm thinking you mean 4566SS9824 and so on.
If that pattern continues, I would recommend using Text to Columns, using S as a delimiter, then adding a column into the middle of the result where you can place the SS back in it's own column. Then you'll have 3 columns. Wit

h that, I would then use a formula like: IF((C2-C1)>1,(C2+C1)/2,""). That is only if your data set holds that current pattern. If it is more complex than that, I could create create a short function for you.

 

[Dazzawm]

I would like a macro to put in my personal macro workbook. I can use on various files then and text to columns and formulas is a little long winded isn't it?

 

[gravanoc]

See if this works.

Sub SequenceFill()

Dim sh As Worksheet


Dim rngFirst As Range
Dim rngLast As Range
Dim rngU As Range
Dim rngNxt As Range
Dim rngD As Range


Dim lgFirst As Long
Dim lgSecond As Long


Set sh = Worksheets("Numb")
Set rngFirst = sh.Range("A1")


For i = 1 To 1048576


    If sh.Range("A" & i).Value = "" Then
    
        Set rngLast = sh.Range("A" & (i - 1))
        Exit For
        
    End If
    
Next


Set rngU = Range(rngFirst, rngLast)
Debug.Print rngU.Address


For Each r In rngU
    
    
    Set rngD = r.Offset(1, 1)
    Set rngNext = r.Offset(1, 0)
    
    If rngNext.Value = "" Then Exit Sub
    lgFirst = Right(r, 4)
    
    lgSecond = Right(rngNext, 4)
    
    If (lgSecond - lgFirst) > 1 Then
       rngD.Value = ((lgSecond + lgFirst) / 2)
    Else
        rngD.Value = ""
    End If
    
Next


End Sub

Note that this only works if the gaps do not exceed 2, but you can tweak it to your needs.

 

[Dazzawm]

Note that this only works if the gaps do not exceed 2, but you can tweak it to your needs.

There could be any amount of gap. The file is so big I wouldn't be able to see with the naked eye?

 

[Dazzawm]

Also on occasions there may be more than 2 letters at the start of the data if that makes a difference.

 

[Peter_SSs]

.. on occasions there may be more than 2 letters at the start of the data.

Edit the 'Const' line in the code to suit the particular data.

I have assumed the first item in the column A list is in cell A2.

If you would have less that 65,536 items in the final list try

Sub List_Missing_1()
  Dim d As Object
  Dim aData As Variant
  Dim Prfx As String
  Dim frst As Long, Lst As Long, i As Long, rws As Long
  
  Const PrefixLength As Long = 2  'Edit to suit your data
  
  Set d = CreateObject("Scripting.Dictionary")
  aData = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  rws = UBound(aData)
  Prfx = Left(aData(1, 1), PrefixLength)
  frst = Mid(aData(1, 1), PrefixLength + 1)
  Lst = Mid(aData(rws, 1), PrefixLength + 1)
  For i = frst To Lst
    d.Add Prfx & i, Null
  Next i
  For i = 1 To rws
    d.Remove aData(i, 1)
  Next i
  Range("D2").Resize(d.Count).Value = Application.Transpose(d.keys)
End Sub

If a larger final list is possible, try

Sub List_Missing_2()
  Dim d As Object
  Dim aData As Variant, aResults As Variant, vKey As Variant
  Dim Prfx As String
  Dim frst As Long, Lst As Long, i As Long, rws As Long
  
  Const PrefixLength As Long = 2  'Edit to suit your data
  
  Set d = CreateObject("Scripting.Dictionary")
  aData = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  rws = UBound(aData)
  Prfx = Left(aData(1, 1), PrefixLength)
  frst = Mid(aData(1, 1), PrefixLength + 1)
  Lst = Mid(aData(rws, 1), PrefixLength + 1)
  For i = frst To Lst
    d.Add Prfx & i, Null
  Next i
  For i = 1 To rws
    d.Remove aData(i, 1)
  Next i
  ReDim aResults(1 To d.Count, 1 To 1)
  i = 0
  For Each vKey In d.keys
    i = i + 1
    aResults(i, 1) = vKey
  Next vKey
  Range("D2").Resize(UBound(aResults)).Value = aResults
End Sub

 

[Dazzawm]

Thanks. When I run code I got a box pop up saying 'Unexpected error (32811)'

 

[Peter_SSs]

Which code?
That doesn't tell us much does it?
I haven't been able to reproduce that error.

 

[Dazzawm]

What else can I say!! A box come up saying exactly that. Both codes.