Code To List Numbers Missing From Sequence

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,375
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
Joined
Oct 20, 2015
Messages
115
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
Joined
Jan 24, 2011
Messages
3,375
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
Joined
Oct 20, 2015
Messages
115
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
Joined
Jan 24, 2011
Messages
3,375
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
Joined
Jan 24, 2011
Messages
3,375
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
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
.. 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
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,375
Thanks. When I run code I got a box pop up saying 'Unexpected error (32811)'
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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
Joined
Jan 24, 2011
Messages
3,375
What else can I say!! A box come up saying exactly that. Both codes.
 
Last edited:

Forum statistics

Threads
1,078,437
Messages
5,340,271
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top