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
113
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
113
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,739
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,739
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,077,855
Messages
5,336,780
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top