Page 1 of 8 123 ... LastLast
Results 1 to 10 of 77

Thread: Code To List Numbers Missing From Sequence
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Code To List Numbers Missing From Sequence

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




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

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




    Thanks
    Last edited by Dazzawm; May 14th, 2019 at 03:07 AM.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  2. #2
    Board Regular
    Join Date
    Oct 2015
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    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. With 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 by gravanoc; May 14th, 2019 at 03:41 AM. Reason: Spelling

  3. #3
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    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?
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  4. #4
    Board Regular
    Join Date
    Oct 2015
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    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 by gravanoc; May 14th, 2019 at 05:33 AM. Reason: Additional comment

  5. #5
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    Quote Originally Posted by gravanoc View Post

    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?
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  6. #6
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    Also on occasions there may be more than 2 letters at the start of the data if that makes a difference.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    Quote Originally Posted by Dazzawm View Post
    .. 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
    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
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    Thanks. When I run code I got a box pop up saying 'Unexpected error (32811)'
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    Which code?
    That doesn't tell us much does it?
    I haven't been able to reproduce that error.
    Last edited by Peter_SSs; May 14th, 2019 at 07:25 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    What else can I say!! A box come up saying exactly that. Both codes.
    Last edited by Dazzawm; May 14th, 2019 at 07:35 AM.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •