Excel 2016: Find Numbers That Are Missing From 5000 to 6000 in one column

Question

I have a column (A) with numbers in order from 5000 to 6000. Now, some numbers are missing. For example number 5004 is missing from the column bellow.

5000
5001
5002
5003
5005
5006
...
6000

So, number 5004 is missing. How can I find all the numbers that are missing?

What excel are you using? do you want the output in one cell or many? — Scott Craner, Mar 15 '17 at 19:52
hello, Scott. I am using Excel 2016. In one cell or many, whatever you want. Maybe both will be ok. — Just Me, Mar 15 '17 at 19:55

Scott Craner · Accepted Answer · 2017-03-15T20:04:19.673

If you want it in many then put this in the first cell and copy/drag down:

=IFERROR(AGGREGATE(15,6,ROW(INDIRECT("5000:6000"))/(ISERROR(MATCH(ROW(INDIRECT("5000:6000")),A:A,0))),ROW(1:1)),"")

If you have Office 365 Excel and you want it in one cell then use this array formula:

=TEXTJOIN(",",TRUE,IF(ISERROR(MATCH(ROW(INDIRECT("5000:6000")),A:A,0)),ROW(INDIRECT("5000:6000")),""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

If you do not have Office 365 Excel and you still want it in one cell then you will need vba.

Put this UDF in a module attached to the workbook, and use the TEXTJOIN() formula described above.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

it is ok, WORKS SUPER ! The first solution is perfect, because I don't have Office 365 Excel. Thank you — Just Me, Mar 15 '17 at 20:07
@JustMe I realize that but just adding the other parts for those who come later. — Scott Craner, Mar 15 '17 at 20:08
by the way, Scott, can u please tell me how to count the numbers of cells containing a specific string / or a number, from a column, without scrolling down? in excel — Just Me, Mar 15 '17 at 21:04

Excel 2016: Find Numbers That Are Missing From 5000 to 6000 in one column

1 Answers1