Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Thread: Code To List Numbers Missing From Sequence

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

    Default Re: Code To List Numbers Missing From Sequence

    This version should work with a variable number of digits and letters. It extracts the numeric part to column B and lists the missing numbers on column D.

    Code:
    Sub DM()
    Dim a, lr, i%, d As Object
    Set d = CreateObject("Scripting.Dictionary")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim a(2 To lr)
    For i = 2 To lr
        a(i) = StrReverse(Val(StrReverse(Cells(i, 1))))
    Next
    [b2].Resize(UBound(a) - 1, 1).Value = WorksheetFunction.Transpose(a)
    For i = WorksheetFunction.Min([b:b]) To WorksheetFunction.Max([b:b])
        d.Add i, i
    Next
    For i = 2 To Range("b" & Rows.Count).End(xlUp).Row
        If d.exists(Cells(i, 2).Value) Then d.Remove Cells(i, 2).Value
    Next
    [d2].Resize(d.Count).Value = WorksheetFunction.Transpose(d.keys)
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


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

    Default Re: Code To List Numbers Missing From Sequence

    Thanks Worf. 2 things. Firstly there is data in column B & C which is why I wanted the missing numbers in column D (I didn't think data in B and C would make a difference to the code), and secondly I really need the prefixes to remain.
    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

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

    Default Re: Code To List Numbers Missing From Sequence

    This version does not write to any auxiliary column.
    As you said the prefix should be the same, I am extracting it from cell A2.

    Code:
    Sub DM()
    Dim a, lr, i%, d As Object, mn%, mx%, pref$
    Set d = CreateObject("Scripting.Dictionary")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim a(2 To lr)
    pref = Mid([a2], 1, Len([a2]) - Len(CStr(Val(StrReverse([a2])))))
    mn = 30000: mx = 0
    For i = 2 To lr
        a(i) = StrReverse(Val(StrReverse(Cells(i, 1))))
        If a(i) < mn Then mn = a(i)
        If a(i) > mx Then mx = a(i)
    Next
    For i = mn To mx
        d.Add pref & i, pref & i
    Next
    For i = LBound(a) To UBound(a)
        If d.exists(pref & a(i)) Then d.Remove pref & a(i)
    Next
    [d2].Resize(d.Count).Value = WorksheetFunction.Transpose(d.keys)
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


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

    Default Re: Code To List Numbers Missing From Sequence

    Thanks Worf, almost there. But there is a problem when I have a set of numbers like below.

    Excel 2010
    A
    2 FASS001
    3 FASS002
    4 FASS003
    5 FASS004
    6 FASS005
    7 FASS006
    8 FASS007
    9 FASS012
    10 FASS016
    11 FASS017
    12 FASS020
    13 FASS021
    14 FASS022
    15 FASS023
    16 FASS024
    17 FASS025
    18 FASS026
    19 FASS027
    20 FASS028
    21 FASS029
    22 FASS030
    23 FASS031
    24 FASS032
    25 FASS033
    26 FASS034
    27 FASS035
    28 FASS036
    29 FASS037
    30 FASS038
    31 FASS039
    32 FASS040
    33 FASS041
    34 FASS042
    35 FASS043
    36 FASS044
    37 FASS045
    38 FASS046
    39 FASS048
    40 FASS049
    41 FASS050
    42 FASS051
    43 FASS052
    44 FASS053
    45 FASS054
    46 FASS055
    47 FASS056
    48 FASS057
    49 FASS058
    50 FASS059
    51 FASS060
    52 FASS061
    53 FASS062
    54 FASS063
    55 FASS064
    56 FASS065
    57 FASS066
    58 FASS069
    59 FASS077
    60 FASS078
    61 FASS079
    62 FASS080
    63 FASS081
    64 FASS082
    65 FASS083
    66 FASS084
    67 FASS085
    68 FASS086
    69 FASS087
    70 FASS090
    71 FASS091
    72 FASS092
    73 FASS093
    74 FASS094
    75 FASS098
    sheet1



    These are the results after the code has run?

    Excel 2010
    D
    2 FASS1
    3 FASS2
    4 FASS3
    5 FASS4
    6 FASS5
    7 FASS6
    8 FASS7
    9 FASS8
    10 FASS9
    11 FASS10
    12 FASS11
    13 FASS12
    14 FASS13
    15 FASS14
    16 FASS15
    17 FASS16
    18 FASS17
    19 FASS18
    20 FASS19
    21 FASS20
    22 FASS21
    23 FASS22
    24 FASS23
    25 FASS24
    26 FASS25
    27 FASS26
    28 FASS27
    29 FASS28
    30 FASS29
    31 FASS30
    32 FASS31
    33 FASS32
    34 FASS33
    35 FASS34
    36 FASS35
    37 FASS36
    38 FASS37
    39 FASS38
    40 FASS39
    41 FASS40
    42 FASS41
    43 FASS42
    44 FASS43
    45 FASS44
    46 FASS45
    47 FASS46
    48 FASS47
    49 FASS48
    50 FASS49
    51 FASS50
    52 FASS51
    53 FASS52
    54 FASS53
    55 FASS54
    56 FASS55
    57 FASS56
    58 FASS57
    59 FASS58
    60 FASS59
    61 FASS60
    62 FASS61
    63 FASS62
    64 FASS63
    65 FASS64
    66 FASS65
    67 FASS66
    68 FASS67
    69 FASS68
    70 FASS69
    71 FASS70
    72 FASS71
    73 FASS72
    74 FASS73
    75 FASS74
    76 FASS75
    77 FASS76
    78 FASS77
    79 FASS78
    80 FASS79
    81 FASS80
    82 FASS81
    83 FASS82
    84 FASS83
    85 FASS84
    86 FASS85
    87 FASS86
    88 FASS87
    89 FASS88
    90 FASS89
    91 FASS90
    92 FASS91
    93 FASS92
    94 FASS93
    95 FASS94
    96 FASS95
    97 FASS96
    98 FASS97
    99 FASS98
    sheet1



    As you can see its not listing the numbers it should for example FASS008, FASS009, it seems to be missing the leading zero?
    Last edited by Dazzawm; Yesterday at 10:44 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
  •