Code To List Numbers Missing From Sequence

Dazzawm

Well-known Member
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
4565SS9822
4566SS9824
4567SS9826
4568SS9828
4569SS9830
4570SS9831
4571SS9833

<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>
</tbody>




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

Excel 2010
D
4565SS9823
4566SS9825
4567SS9827
4568SS9829
4569SS9832

<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>
</tbody>




Thanks
 
Last edited:

gravanoc

Board Regular
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.


 
Last edited:

Dazzawm

Well-known Member
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

Board Regular
See if this works.

Code:
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.
 
Last edited:

Dazzawm

Well-known Member
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

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

Peter_SSs

MrExcel MVP, Moderator
.. 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
Rich (BB code):
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
Rich (BB code):
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
 

Peter_SSs

MrExcel MVP, Moderator
Which code?
That doesn't tell us much does it? ;)
I haven't been able to reproduce that error.
 
Last edited:

Dazzawm

Well-known Member
What else can I say!! A box come up saying exactly that. Both codes.
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top