Showing missing numbers in a sequence

[Cruiser69]

Hi all.

Could anyone help with showing missing numbers in a sequence

I have been using the code below which works well.

Sub Missing_Numbers_in_Sequence()


' This loops through Column A and checks for missing numbers in a sequence.
' Then shows the missing numbers in column C

   Dim iLoop As Long, iLoop2 As Long
Dim Last_Row As Long
Dim Old_Number As Long, New_Number As Long

Last_Row = Range("A5000").End(xlUp).Row
Old_Number = Val(Right(ActiveSheet.Range("A1").Value, 5))
For iLoop = 1 To Last_Row
    New_Number = Val(Right(Worksheets(1).Range("A" & iLoop).Value, 5))
    For iLoop2 = Old_Number To (New_Number - 2)
        ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = _
            "" & iLoop2 + 1 & ""
    Next iLoop2
    Old_Number = New_Number
Next iLoop


End Sub

The example below shows just 5 & 7 missing, which is correct

A	B	C
Number	Description	Missing Numbers
1	Chair	5
2	Speaker	7
3	Phone
4	TV
6	Table
8	Soundbar

If the number does not start at 1 as below, it shows all numbers from 1. Then shows the number that is really missing, which is 13

A	B	C
Number	Description	Missing Numbers
8	Chair	1
9	Speaker	2
10	Phone	3
11	TV	4
12	Table	5
14	Phone	6
		7
		13

Is there a way to just show the missing numbers from the first number entered


Thanks for looking,


Graham

 

[steve the fish]

See if changing this line:

ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = "" & iLoop2 + 1 & ""

to this:

If iLoop2 + 1 > Application.Min(Range("A1:A" & Last_Row)) Then
    ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = "" & iLoop2 + 1 & ""
End If

 

[Cruiser69]

Thanks Steve.
That is awesome.

 

[James006]

Hello,

Below is a macro for the missing values

Sub MissingValues()
Dim rng As Range, j As Variant
Dim StartV As Long, EndV As Long, i As Long
Dim k() As Long, Last_Row As Long

Last_Row = Range("A5000").End(xlUp).Row
ReDim k(0)

Set rng = Range("A2:A" & Last_Row)
StartV = Range("A2")
EndV = Range("A" & Last_Row)

    For i = StartV To EndV
        On Error Resume Next
        j = Application.Match(i, rng, 0)
        If IsError(j) Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Next i
Range("C1") = "Missing values"
Range("C2:C" & UBound(k) + 1) = Application.Transpose(k)
End Sub

Hope this will help

 

[Peter_SSs]

You could also do it with a worksheet formula. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Book1
	A	B	C
1	Number		Missing
2	8		13
3	9		15
4	10		16
5	11		17
6	12		19
7	14
8	18
9	20
10
Missing Numbers (2)

Cell Formulas
Range	Formula
C2:C9	C2	{=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX(A:A,A$2):INDEX(A:A,LOOKUP(9.99E+307,A:A))),A$2:A$100,0)),ROW(INDEX(A:A,A$2):INDEX(A:A,LOOKUP(9.99E+307,A:A))),""),ROWS(C$2:C2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Cruiser69]

Thanks for all you replies

Graham

 

[Peter_SSs]

You're welcome. You have some choices.

 

[Sambecko]

Hi All,
I'm still learning to make macros, I want to change the macro that @James006 made, the data above becomes horizontal from the range A2:J2 and the results to the range M2:V2 starting from numbers 0 to 9. Please help.

 

[Dave Patton]

T202406a.xlsm
	A	B	C	D
1	Number		Missing
2	8
3	9		13	13
4	10		15	15
5	11		16	16
6	12		17	17
7	14		19	19
8	18
9	20
4a

Cell Formulas
Range	Formula
C3:C7	C3	=LET(a,SEQUENCE(13,,8,1),FILTER(a,NOT(COUNTIF(A2:A9,a))))
D3:D7	D3	=LET(L,A2:A9,a,SEQUENCE(MAX(L)-MIN(L)+1,,MIN(L),1),FILTER(a,NOT(COUNTIF(L,a))))
Dynamic array formulas.

T202406a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	0	1		3	4		6	7	8	9
3											2	5
4
4aa

Cell Formulas
Range	Formula
K3:L3	K3	=TRANSPOSE(LET(x,A2:J2,a,SEQUENCE(MAX(x)-MIN(x)+1,,MIN(x),1),FILTER(a,NOT(COUNTIF(x,a)))))
Dynamic array formulas.