Code To List Numbers Missing From Sequence

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
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("[B][COLOR=#FF0000]C[/COLOR][/B]1").Value = "Missing Nums"
  Range("[B][COLOR=#FF0000]C[/COLOR][/B]2").Resize(UBound(Nums)) = Nums
  On Error GoTo Whoops
  With Range("[B][COLOR=#FF0000]C[/COLOR][/B]2", Cells(Rows.Count, "[B][COLOR=#FF0000]C[/COLOR][/B]").End(xlUp))
    .SpecialCells(xlBlanks).Delete xlShiftUp
    .Value = Evaluate("IF(" & .Address & "="""","""",""" & PreFix & """&TEXT(" & .Address & ",""000""))")
  End With
Whoops:
  Application.ScreenUpdating = True
End Sub
I have tried this on another file not too dissimilar to what I sent you and I get a run-time error '9' subscript out of range and it points to this line?

Nums(Val(Mid(Data(X, 1), PrefixLen + 1)), 1) = ""
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
Also I am not sure if it makes a difference but the way excel sorts might mess things up like below. You have SS950 and instead of then SS951 it goes to SS9500.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6467</td><td style=";">SS9499</td></tr><tr ><td style="color: #161120;text-align: center;">6468</td><td style=";">SS950</td></tr><tr ><td style="color: #161120;text-align: center;">6469</td><td style=";">SS9500</td></tr><tr ><td style="color: #161120;text-align: center;">6470</td><td style=";">SS9501</td></tr><tr ><td style="color: #161120;text-align: center;">6471</td><td style=";">SS9502</td></tr><tr ><td style="color: #161120;text-align: center;">6472</td><td style=";">SS9503</td></tr><tr ><td style="color: #161120;text-align: center;">6473</td><td style=";">SS9504</td></tr><tr ><td style="color: #161120;text-align: center;">6474</td><td style=";">SS9505</td></tr><tr ><td style="color: #161120;text-align: center;">6475</td><td style=";">SS9506</td></tr><tr ><td style="color: #161120;text-align: center;">6476</td><td style=";">SS9507</td></tr><tr ><td style="color: #161120;text-align: center;">6477</td><td style=";">SS9508</td></tr><tr ><td style="color: #161120;text-align: center;">6478</td><td style=";">SS9509</td></tr><tr ><td style="color: #161120;text-align: center;">6479</td><td style=";">SS951</td></tr><tr ><td style="color: #161120;text-align: center;">6480</td><td style=";">SS9510</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">FILE.0071</p><br /><br />
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,008
What happens if you run the code from post 37?
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
What happens if you run the code from post 37?
There were about half a dozen columns with data in and the one column with the missing numbers.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,183
Office Version
2010
Platform
Windows
Also I am not sure if it makes a difference but the way excel sorts might mess things up like below. You have SS950 and instead of then SS951 it goes to SS9500.
Yes, that affected my code as I assumed the largest number would always be at the bottom of your data. Here is revised code (runs a bit slower) that should work (still outputs to Column C in case that makes a difference)...
Code:
[table="width: 500"]
[tr]
	[td]Sub MissingNumbers()
  Dim X As Long, FirstNum As Long, LastNum As Long, MaxNum 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)
  For X = 1 To UBound(Data)
    If Mid(Data(X, 1), PrefixLen + 1) > MaxNum Then MaxNum = Val(Mid(Data(X, 1), PrefixLen + 1))
  Next
  FirstNum = Val(Mid(Data(1, 1), PrefixLen + 1))
  LastNum = Val(Mid(Data(UBound(Data), 1), PrefixLen + 1))
  Nums = Evaluate("ROW(1:" & MaxNum & ")")
  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
  Columns("C").Clear
  Range("[B][COLOR="#FF0000"]C[/COLOR][/B]1").Value = "Missing Nums"
  Range("[B][COLOR="#FF0000"]C[/COLOR][/B]2").Resize(UBound(Nums)) = Nums
  On Error GoTo Whoops
  With Range("[B]C[/B]2", Cells(Rows.Count, "[B][COLOR="#FF0000"]C[/COLOR][/B]").End(xlUp))
    .SpecialCells(xlBlanks).Delete xlShiftUp
    .Value = Evaluate("IF(" & .Address & "="""","""",""" & PreFix & """&TEXT(" & .Address & ",""000""))")
  End With
Whoops:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
Yes, that affected my code as I assumed the largest number would always be at the bottom of your data. Here is revised code (runs a bit slower) that should work (still outputs to Column C in case that makes a difference)...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MissingNumbers()
  Dim X As Long, FirstNum As Long, LastNum As Long, MaxNum 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)
  For X = 1 To UBound(Data)
    If Mid(Data(X, 1), PrefixLen + 1) > MaxNum Then MaxNum = Val(Mid(Data(X, 1), PrefixLen + 1))
  Next
  FirstNum = Val(Mid(Data(1, 1), PrefixLen + 1))
  LastNum = Val(Mid(Data(UBound(Data), 1), PrefixLen + 1))
  Nums = Evaluate("ROW(1:" & MaxNum & ")")
  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
  Columns("C").Clear
  Range("[B][COLOR=#FF0000]C[/COLOR][/B]1").Value = "Missing Nums"
  Range("[B][COLOR=#FF0000]C[/COLOR][/B]2").Resize(UBound(Nums)) = Nums
  On Error GoTo Whoops
  With Range("[B]C[/B]2", Cells(Rows.Count, "[B][COLOR=#FF0000]C[/COLOR][/B]").End(xlUp))
    .SpecialCells(xlBlanks).Delete xlShiftUp
    .Value = Evaluate("IF(" & .Address & "="""","""",""" & PreFix & """&TEXT(" & .Address & ",""000""))")
  End With
Whoops:
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Working great. Can I ask does it matter how many letters are at the start or end of each number? Also in a file do the prefixes have to be all the same?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,183
Office Version
2010
Platform
Windows
Can I ask does it matter how many letters are at the start or end of each number?
For my code... no, it does not matter.


Also in a file do the prefixes have to be all the same?
For my code... yes, the prefixes must all be the same. I could probably modify the code to handle different prefixes so long as each individual prefix was grouped together with all of the other same individual prefix (although I would expect the code to be slightly more complex and more sluggish)..
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
Many thanks for your help Rick, I will keep all the prefixes the same in each file.
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
Hi Rick. I am getting subscript 9 error again and it points to this row

Nums(Val(Mid(Data(X, 1), PrefixLen + 1)), 1) = ""

Does it matter if some cells have letters on the end and others don't?

Also now the numbers have gone into 5 characters i.e FASS10001 whereas when you did the code it was 4 i.e FASS9998
 
Last edited:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,398
Office Version
2016
Platform
Windows
After running a couple of tests it seems to be when there is more than 8 characters in the cell.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,961
Messages
5,508,412
Members
408,683
Latest member
JordanStach

This Week's Hot Topics

Top