Code To List Numbers Missing From Sequence

[Dazzawm]

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
SS9822
SS9824
SS9826
SS9828
SS9830
SS9831
SS9833

<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>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

[TD="align: center"]4570[/TD]

[TD="align: center"]4571[/TD]

</tbody>

​

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

Excel 2010

	D
SS9823
SS9825
SS9827
SS9829
SS9832

<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>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

</tbody>

​

Thanks

 

[Worf]

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.

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

 

[Dazzawm]

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.

 

[Worf]

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.

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

 

[Dazzawm]

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

Excel 2010

	A
FASS001
FASS002
FASS003
FASS004
FASS005
FASS006
FASS007
FASS012
FASS016
FASS017
FASS020
FASS021
FASS022
FASS023
FASS024
FASS025
FASS026
FASS027
FASS028
FASS029
FASS030
FASS031
FASS032
FASS033
FASS034
FASS035
FASS036
FASS037
FASS038
FASS039
FASS040
FASS041
FASS042
FASS043
FASS044
FASS045
FASS046
FASS048
FASS049
FASS050
FASS051
FASS052
FASS053
FASS054
FASS055
FASS056
FASS057
FASS058
FASS059
FASS060
FASS061
FASS062
FASS063
FASS064
FASS065
FASS066
FASS069
FASS077
FASS078
FASS079
FASS080
FASS081
FASS082
FASS083
FASS084
FASS085
FASS086
FASS087
FASS090
FASS091
FASS092
FASS093
FASS094
FASS098

<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>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

[TD="align: center"]49[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]51[/TD]

[TD="align: center"]52[/TD]

[TD="align: center"]53[/TD]

[TD="align: center"]54[/TD]

[TD="align: center"]55[/TD]

[TD="align: center"]56[/TD]

[TD="align: center"]57[/TD]

[TD="align: center"]58[/TD]

[TD="align: center"]59[/TD]

[TD="align: center"]60[/TD]

[TD="align: center"]61[/TD]

[TD="align: center"]62[/TD]

[TD="align: center"]63[/TD]

[TD="align: center"]64[/TD]

[TD="align: center"]65[/TD]

[TD="align: center"]66[/TD]

[TD="align: center"]67[/TD]

[TD="align: center"]68[/TD]

[TD="align: center"]69[/TD]

[TD="align: center"]70[/TD]

[TD="align: center"]71[/TD]

[TD="align: center"]72[/TD]

[TD="align: center"]73[/TD]

[TD="align: center"]74[/TD]

[TD="align: center"]75[/TD]

</tbody>

sheet1​

These are the results after the code has run?

Excel 2010

	D
FASS1
FASS2
FASS3
FASS4
FASS5
FASS6
FASS7
FASS8
FASS9
FASS10
FASS11
FASS12
FASS13
FASS14
FASS15
FASS16
FASS17
FASS18
FASS19
FASS20
FASS21
FASS22
FASS23
FASS24
FASS25
FASS26
FASS27
FASS28
FASS29
FASS30
FASS31
FASS32
FASS33
FASS34
FASS35
FASS36
FASS37
FASS38
FASS39
FASS40
FASS41
FASS42
FASS43
FASS44
FASS45
FASS46
FASS47
FASS48
FASS49
FASS50
FASS51
FASS52
FASS53
FASS54
FASS55
FASS56
FASS57
FASS58
FASS59
FASS60
FASS61
FASS62
FASS63
FASS64
FASS65
FASS66
FASS67
FASS68
FASS69
FASS70
FASS71
FASS72
FASS73
FASS74
FASS75
FASS76
FASS77
FASS78
FASS79
FASS80
FASS81
FASS82
FASS83
FASS84
FASS85
FASS86
FASS87
FASS88
FASS89
FASS90
FASS91
FASS92
FASS93
FASS94
FASS95
FASS96
FASS97
FASS98

<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>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

[TD="align: center"]49[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]51[/TD]

[TD="align: center"]52[/TD]

[TD="align: center"]53[/TD]

[TD="align: center"]54[/TD]

[TD="align: center"]55[/TD]

[TD="align: center"]56[/TD]

[TD="align: center"]57[/TD]

[TD="align: center"]58[/TD]

[TD="align: center"]59[/TD]

[TD="align: center"]60[/TD]

[TD="align: center"]61[/TD]

[TD="align: center"]62[/TD]

[TD="align: center"]63[/TD]

[TD="align: center"]64[/TD]

[TD="align: center"]65[/TD]

[TD="align: center"]66[/TD]

[TD="align: center"]67[/TD]

[TD="align: center"]68[/TD]

[TD="align: center"]69[/TD]

[TD="align: center"]70[/TD]

[TD="align: center"]71[/TD]

[TD="align: center"]72[/TD]

[TD="align: center"]73[/TD]

[TD="align: center"]74[/TD]

[TD="align: center"]75[/TD]

[TD="align: center"]76[/TD]

[TD="align: center"]77[/TD]

[TD="align: center"]78[/TD]

[TD="align: center"]79[/TD]

[TD="align: center"]80[/TD]

[TD="align: center"]81[/TD]

[TD="align: center"]82[/TD]

[TD="align: center"]83[/TD]

[TD="align: center"]84[/TD]

[TD="align: center"]85[/TD]

[TD="align: center"]86[/TD]

[TD="align: center"]87[/TD]

[TD="align: center"]88[/TD]

[TD="align: center"]89[/TD]

[TD="align: center"]90[/TD]

[TD="align: center"]91[/TD]

[TD="align: center"]92[/TD]

[TD="align: center"]93[/TD]

[TD="align: center"]94[/TD]

[TD="align: center"]95[/TD]

[TD="align: center"]96[/TD]

[TD="align: center"]97[/TD]

[TD="align: center"]98[/TD]

[TD="align: center"]99[/TD]

</tbody>

sheet1​

As you can see its not listing the numbers it should for example FASS008, FASS009, it seems to be missing the leading zero?

 

[Worf]

Therefore, the code should deal with leading zeros and strings of variable length, as shown below. I will work on it.

Excel Workbook
	A
1	data
2	SS010
3	SS013
4	SS016
5	SS0098
6	SS0101
7	SS0104
8	SS0107
table of values

 

[Dazzawm]

OK thanks worf

 

[Worf]

This test code shows the method I am proposing, note that columns G and K hold the missing data.
I will be back later with the final version.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	data	len			data	len			data	len			len	len
2	SS010	5	VERDADEIRO		SS010	5	SS011	FALSO	SS0098	6	SS0099			5
3	SS013	5			SS013	5	SS012		SS0101	6	SS0100			6
4	SS016	5			SS016	5	SS014		SS0104	6	SS0102
5	SS0098	6					SS015		SS0107	6	SS0103
6	SS0101	6									SS0105
7	SS0104	6									SS0106
8	SS0107	6
table of values

Sub main()
DM [f2], [e2], [g2]
DM [j2], [i2], [k2]
End Sub


Sub DM(totrange As Range, drng 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(drng.Address, "$")(1) & Rows.count).End(xlUp).Row
If lr > 20 Then Exit Sub
ReDim a(2 To lr)
j = 0
Do
    j = j + 1
Loop While Not IsNumeric(Mid(drng, j, 1)) And j < 20
j = j - 1
pref = Left(drng, j)
mn = 30000: mx = 0
For i = 2 To lr
    a(i) = Right(Cells(i, drng.Column), Len(Cells(i, drng.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, drng.Column).Value) Then d.Remove Cells(i, drng.Column).Value
Next
dest.Resize(d.count).Value = WorksheetFunction.Transpose(d.Keys)
End Sub

 

[Worf]

This version uses an auxiliary sheet for calculations:

<strike></strike>

Sub Satv()
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.[a1]
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, drng 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(drng.Address, "$")(1) & Rows.Count).End(xlUp).Row
ReDim a(2 To lr)
j = 0
Do
    j = j + 1
Loop While Not IsNumeric(Mid(drng, j, 1)) And j < 20
j = j - 1
pref = Left(drng, j)
mn = 30000: mx = 0
For i = 2 To lr
    a(i) = Right(Cells(i, drng.Column), Len(Cells(i, drng.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, drng.Column).Value) Then d.Remove Cells(i, drng.Column).Value
Next
dest.Resize(d.Count).Value = WorksheetFunction.Transpose(d.Keys)
End Sub

 

[Dazzawm]

Thanks worf, when I run the above I get 'subscript out of range'?

 

[Worf]

On what code line?
Do you have worksheets named Sheet1 and Plan2, as explained in the code comments?