Code To List Numbers Missing From Sequence
Page 4 of 8 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 77

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

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

    Default Re: Code To List Numbers Missing From Sequence

    I now get a box come up with a 400 in. And when I select ok sheet 1 ends up as below.

    Excel 2010#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">
    ABC
    1Plan255
    20

    #DAE7F5 ;color: #161120">sheet1





    And plan2 is as below.

    Excel 2010#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">
    ABCD
    1Plan2Result

    #DAE7F5 ;color: #161120">plan2



    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. #32
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,678
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    How many rows are there in column A? Can you email me the workbook?
    I need to see the input data.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


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

    Default Re: Code To List Numbers Missing From Sequence

    Quote Originally Posted by Worf View Post
    How many rows are there in column A? Can you email me the workbook?
    I need to see the input data.
    Can you PM me your email please
    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. #34
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,678
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    There is data in the format SS2311XT.
    Should the code ignore the letters on the right side?
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


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

    Default Re: Code To List Numbers Missing From Sequence

    Yes, sorry I didn't realise they were in there

  6. #36
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,678
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    I will rewrite the code so that it removes the letters on the right.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  7. #37
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,678
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    • The parts after the letter removal were already there, so the code is simply excluding parts that end with letters.
    • 5266 missing parts were found.
    • Note that Sheet1 should be a blank sheet, while Plan2 holds the part list; of course these names can be changed.


    Code:
    Sub Satv()                                          ' run me
    Dim orig As Worksheet, aux As Worksheet, lr%, bsr As Range, i%
    Set aux = Sheets("sheet1")                          ' auxiliary sheet
    Set orig = Sheets("plan2")                          ' original sheet
    orig.[d:d].ClearContents
    orig.[d1] = "Result"
    aux.Activate
    Cells.ClearContents
    orig.[a:a].Copy aux.[aa1]
    [ab2].Formula = "=not(iserr(value(right(aa2,1))))"  ' no letter on the right side
    Range("aa:aa").AdvancedFilter xlFilterCopy, [ab1:ab2], [a1], True
    lr = Range("a" & Rows.Count).End(xlUp).Row
    [b1] = "Len"
    [b2].FormulaR1C1 = "=LEN(RC[-1])"
    [b2].AutoFill Destination:=Range("B2:B" & lr), Type:=xlFillDefault
    [c1] = [b1]
    Range("b1:b" & lr).AdvancedFilter xlFilterCopy, [c1:c2], [d1], True
    Set bsr = [e1]
    For i = 2 To Range("d" & Rows.Count).End(xlUp).Row
        bsr.Offset(1).Formula = "=b2=" & Cells(i, 4)
        Range("a1:b" & lr).AdvancedFilter xlFilterCopy, bsr.Resize(2, 1), bsr.Offset(, 1), False
        DM bsr.Offset(1, 2), bsr.Offset(1, 1), bsr.Offset(1, 3)
        Range(Cells(2, bsr.Offset(, 3).Column), Cells(Range(Split(bsr.Offset(, 3).Address, "$")(1) _
        & Rows.Count).End(xlUp).Row, bsr.Offset(, 3).Column)).Copy _
        orig.Cells(orig.Range("d" & Rows.Count).End(xlUp).Row + 1, 4)
        Set bsr = bsr.Offset(, 4)
    Next
    End Sub
    
    
    Sub DM(totrange As Range, dr As Range, dest As Range)
    Dim a, lr, i%, d As Object, mn%, mx%, pref$, it, j%
    Set d = CreateObject("Scripting.Dictionary")
    lr = Range(Split(dr.Address, "$")(1) & Rows.Count).End(xlUp).Row
    ReDim a(2 To lr)
    j = 0
    Do
        j = j + 1
    Loop While Not IsNumeric(Mid(dr, j, 1)) And j < 20
    j = j - 1
    pref = Left(dr, j)
    mn = 30000: mx = 0
    For i = 2 To lr
        a(i) = Right(Cells(i, dr.Column), Len(Cells(i, dr.Column)) - j)
        If a(i) < mn Then mn = a(i)
        If a(i) > mx Then mx = a(i)
    Next
    For i = mn To mx
        it = pref & WorksheetFunction.Rept("0", totrange.Value - Len(pref & i)) & i
        d.Add it, it
    Next
    For i = 2 To lr
        If d.Exists(Cells(i, dr.Column).Value) Then d.Remove Cells(i, dr.Column).Value
    Next
    dest.Resize(d.Count).Value = WorksheetFunction.Transpose(d.Keys)
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


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

    Default Re: Code To List Numbers Missing From Sequence

    Thanks I will try at work tomorrow and let you know.

  9. #39
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,991
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Code To List Numbers Missing From Sequence

    I think the following macro will do what you want. Note that it assumes the same prefix for all values in Column A and that, since you did not say, the output will go to Column C (change the red letters to the column letter designation of the desired output column if different than my guess)...
    Code:
    Sub MissingNumbers()
      Dim X As Long, FirstNum As Long, LastNum As Long, PrefixLen As Long
      Dim PreFix As String, Nums As Variant, Data As Variant
      Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
      PrefixLen = [MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))] - 1
      PreFix = Left(Data(1, 1), PrefixLen)
      FirstNum = Val(Mid(Data(1, 1), PrefixLen + 1))
      LastNum = Val(Mid(Data(UBound(Data), 1), PrefixLen + 1))
      Nums = Evaluate("ROW(1:" & LastNum & ")")
      For X = 1 To UBound(Data)
        If X < FirstNum Then
          Nums(X, 1) = ""
        Else
          Nums(Val(Mid(Data(X, 1), PrefixLen + 1)), 1) = ""
        End If
      Next
      Application.ScreenUpdating = False
      Range("C1").Value = "Missing Nums"
      Range("C2").Resize(UBound(Nums)) = Nums
      On Error GoTo Whoops
      With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        .SpecialCells(xlBlanks).Delete xlShiftUp
        .Value = Evaluate("IF(" & .Address & "="""","""",""" & PreFix & """&TEXT(" & .Address & ",""000""))")
      End With
    Whoops:
      Application.ScreenUpdating = True
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Code To List Numbers Missing From Sequence

    Thanks Rick seems to work perfect. Thanks for all the time you have put into it also Worf. It seems to give some strange results in some columns but there is a column with the missing numbers. Its amazing with VBA how both codes are so different but in the end it gives you the same result. If I have any problems with any other files I use it on I will let you know. Thanks both again.
    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
  •