Code To List Numbers Missing From Sequence

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
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:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
144
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,381
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
144
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,381
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,381
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
43,675
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,381
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
43,675
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,381
What else can I say!! A box come up saying exactly that. Both codes.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,228
Messages
5,413,204
Members
403,466
Latest member
sammv

This Week's Hot Topics

Top