How to detect missing numbers within a list?

happydz

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hello. Not that I can think of.

If you define precisely what is required, it can be done by code.
 
Upvote 0
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.
 
Upvote 0
.
Eric:

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

???
 
Upvote 0
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.
 
Upvote 0
.
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top