How to detect missing numbers within a list?

[happydz]

I have a column of serial numbers that are randomly classified. Is there an option in Excel that detects the missing numbers within this list?
Thank you

[TABLE="width: 500"]
[TR]
[TD][TABLE="width: 80"]
[TR]
[TD="class: xl65, width: 80"]NUM[/TD]
[/TR]
[TR]
[TD="class: xl66"]4001[/TD]
[/TR]
[TR]
[TD="class: xl66"]4003[/TD]
[/TR]
[TR]
[TD="class: xl66"]4004[/TD]
[/TR]
[TR]
[TD="class: xl66"]4005[/TD]
[/TR]
[TR]
[TD="class: xl66"]4006[/TD]
[/TR]
[TR]
[TD="class: xl66"]4007[/TD]
[/TR]
[TR]
[TD="class: xl66"]4008[/TD]
[/TR]
[TR]
[TD="class: xl66"]4009[/TD]
[/TR]
[TR]
[TD="class: xl66"]4010[/TD]
[/TR]
[TR]
[TD="class: xl66"]4011[/TD]
[/TR]
[TR]
[TD="class: xl66"]4013[/TD]
[/TR]
[TR]
[TD="class: xl66"]4014[/TD]
[/TR]
[TR]
[TD="class: xl66"]4015[/TD]
[/TR]
[TR]
[TD="class: xl66"]4016[/TD]
[/TR]
[TR]
[TD="class: xl66"]4017[/TD]
[/TR]
[TR]
[TD="class: xl66"]4018[/TD]
[/TR]
[TR]
[TD="class: xl66"]4019[/TD]
[/TR]
[TR]
[TD="class: xl66"]4020[/TD]
[/TR]
[TR]
[TD="class: xl66"]4023[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

 

[Fazza]

Hello. Not that I can think of.

If you define precisely what is required, it can be done by code.

 

[Eric W]

[TABLE="class: grid, width: 500"]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NUM[/TD]
[TD]Missing Numbers[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4001[/TD]
[TD]4002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4003[/TD]
[TD]4012[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4004[/TD]
[TD]4021[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4005[/TD]
[TD]4022[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4006[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4009[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4020[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]4023[/TD]
[TD][/TD]
[/TR]
[/TABLE]


B2:

=IFERROR(AGGREGATE(15,6,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))/(COUNTIF(A$2:A$20,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))))+COUNTIF(B$1:B1,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))))=0),1),"")

This should work for positive integers below about 1,000,000.

 

[Logit]

.
Eric:

Following this interesting thread. Tried the formula but it doesn't produce any numbers here.

???

 

[Eric W]

Hmm. Not sure why you're not getting any numbers. This thread has HTML turned off, so I had to just make a table, but I assure you, it works on my test sheet. I assume your version of Excel has AGGREGATE? I'll have to cogitate a bit to see if I can think of any other reasons.

 

[Logit]

.
That is most likely it. Using ver 2007. I type in the term AGGREGATE and nothing happens like it usually does where
Excel provides auto suggestions for the formula term.

(found a few websites that indicate ver 2003 and 2007 don't have AGGREGATE)

 

[Eric W]

Here's how to do it with Excel 2007. According to my references, IFERROR is available:

=IFERROR(SMALL(IFERROR(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))/(COUNTIF(A$2:A$20,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))))+COUNTIF(B$1:B1,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))))=0),""),1),"")

This requires being entered with Control+Shift+Enter.

 

[Logit]

.
BINGO !!!

Thanks Eric.

 

[Peter_SSs]

This thread has HTML turned off,..

FYI: The thread has now been moved to the Excel Questions forum as that seems to be where it should be, so now HTML is available should you want.

 

[Peter_SSs]

Here are some more compact alternatives that also avoid the volatile function INDIRECT. I have not used MIN & MAX to find the endpoints since the OP's data is in ascending order.

Excel Workbook
	A	B	C
1	NUM	Missing Numbers	Missing Numbers
2	4001	4002	4002
3	4003	4012	4012
4	4004	4021	4021
5	4005	4022	4022
6	4006
7	4007
8	4008
9	4009
10	4010
11	4011
12	4013
13	4014
14	4015
15	4016
16	4017
17	4018
18	4019
19	4020
20	4023
21
Sheet2 (2)