How to detect missing numbers within a list?

happydz

New Member
Joined
Jan 11, 2017
Messages
14
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

NUM
4001
4003
4004
4005
4006
4007
4008
4009
4010
4011
4013
4014
4015
4016
4017
4018
4019
4020
4023
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hello. Not that I can think of.

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,423
AB
1NUMMissing Numbers
240014002
340034012
440044021
540054022
64006
74007
84008
94009
104010
114011
124013
134014
144015
154016
164017
174018
184019
194020
204023


B2:
Code:
=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

Well-known Member
Joined
Aug 31, 2016
Messages
4,157
.
Eric:

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

???
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,423

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2016
Messages
4,157
.
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)
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,423

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,656
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,656
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1NUMMissing NumbersMissing Numbers
2400140024002
3400340124012
4400440214021
5400540224022
64006
74007
84008
94009
104010
114011
124013
134014
144015
154016
164017
174018
184019
194020
204023
21
Sheet2 (2)
 

Forum statistics

Threads
1,141,705
Messages
5,707,973
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top