Indicate missing number in a sequence

[Beth]

can somone please assist me what formula I can use to find the missing in a
sequence number?

see example below. How could I find the missing 1327, 1330 and 1333?

Order #
1325
1326
1328
1329
1331
1332
1334
1335

Any help will be appreciated.

Thank youj,
Beth

 

[Ragdyer]

Would this work for you?

With your numbers in A1 to A8,
enter this in B2 and copy down:

=IF(A1+1=A2,"",A1+1)

 

[Don Guillett]

Sub findmissingnumbersinlist()
mc = 1 'for col A
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
MsgBox "Missing " & Cells(i, mc) + 1 & " at row " & i + 1
End If
Next i
End Sub

 

[Beth]

Thank you for your help on this. It is not working. The header is on A1 and
the first data starts from A2. Where do I need to start typing the formula
(the first formula)?

Thank you,
Beth

 

[xlm]

Hi Beth

I think you need to change Ragdyer's formula to this if you have a header in
A1and place it in B3, then copy down. You will need to input the first value
in B2 which is 1325.
=IF(A2+1=A2,"",A2+1)

--
HTH

Appreciate that you click the Yes button below if this posting is helpful

cheers, francis

 

[Beth]

Thank you, xlm.

by using your formula I got below missing numbers. could you please review
and advise.

Order # Missing Numbers
1325 1326
1326 1327
1328 1329
1329 1330
1331 1332
1332 1333
1334 1335
1335 1336
1336 1337
1337 1338

Thank you,
Beth

 

[Ron Rosenfeld]

can somone please assist me what formula I can use to find the missing in a
sequence number?

see example below. How could I find the missing 1327, 1330 and 1333?

Order #
1325
1326
1328
1329
1331
1332
1334
1335

Any help will be appreciated.

Thank youj,
Beth

All of the following are **array** formulas. After pasting them into a cell,
they must be entered by holding down <ctrl><shift> while hitting <enter>. If
you do this correctly, Excel will place braces {...} around the formula.

In the formulas below, "Order" refers to the range in which you have these
values listed. I NAME'd a sample range, but you could substitute an absolute
reference (e.g. $A$2:$A$16)

For the missing values in descending order, enter:

=LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1))

and copy/drag down until you begin to get #NUM! errors

For the missing values in ascending order, enter:

=LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1))

and copy/drag down until you begin to get #NUM! errors


If you want to avoid the error messages, you can use one of the following
formulas instead:

If you are running Excel 2007:


Descending order:

=IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1)),"")

Ascending order:

=IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1)),"")

If you are running a version of Excel prior to 2007:

Descending order:

=IF(ISERR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1)))

Ascending order:

&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1)))

--ron

 

[T. Valko]

Try this array formula** :

Assuming your numbers are in the range A2:A9

The sequence you want to check is from 1325 to 1335.

Array entered** in B2:

=SMALL(IF(ISNA(MATCH(ROW(A$1325:A$1335),A$2:A$9,0)),ROW(A$1325:A$1335)),ROWS(B$2:B2))

Copy down until you #NUM! errors meaning all missing numbers have been
returned.

Note: this method is slow to calculate on very large sequences. Also note
that you're limited to sequences with a maximum end value that is equal to
the number or rows that a worksheet has which is dependent upon what version
of Excel you're using.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

All of the following are **array** formulas. After pasting them into a
cell,
they must be entered by holding down <ctrl><shift> while hitting <enter>.
If
you do this correctly, Excel will place braces {...} around the formula.

In the formulas below, "Order" refers to the range in which you have these
values listed. I NAME'd a sample range, but you could substitute an
absolute
reference (e.g. $A$2:$A$16)

For the missing values in descending order, enter:

=LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1))

and copy/drag down until you begin to get #NUM! errors

For the missing values in ascending order, enter:

=LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1))

and copy/drag down until you begin to get #NUM! errors


If you want to avoid the error messages, you can use one of the following
formulas instead:

If you are running Excel 2007:


Descending order:

=IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1)),"")

Ascending order:

=IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1)),"")

If you are running a version of Excel prior to 2007:

Descending order:

=IF(ISERR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1)))

Ascending order:

&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""))+1-ROWS($1:1)))

--ron

Assuming no numbers are duplicated.

A more efficient error trap for versions prior to Excel 2007 might be:

=IF(ROWS(B$2:B2)<=MAX(Order)-MIN(Order)+1-COUNT(Order),.....

 

[xlm]

Hi Beth

Did you type the number 1325 into cell B2? and place the formula in cell B3?
Otherwise, if you just want to find the missing number, David's formula will
do that.

--
HTH

Appreciate that you click the Yes button below if this posting is helpful

cheers, francis

 

[Ron Rosenfeld]

**Assuming no numbers are duplicated.**

That's the key point, of course

A more efficient error trap for versions prior to Excel 2007 might be:

=IF(ROWS(B$2:B2)<=MAX(Order)-MIN(Order)+1-COUNT(Order),.....

I really like the IFERROR function in 2007.
--ron

 

[Max]

Another alternative tinker to try out ...

Assuming your data in A2 down,
with starting number in A2

Put in B3:
=IF(ROWS($1:1)+$A$2>MAX(A:A),"Stop",IF(ISNUMBER(MATCH(ROWS($1:1)+$A$2,A:A,0)),"",ROWS($1:1)+$A$2))
Copy B3 down until "Stop" appears. Leave B1:B2 empty

Then put in C3:
=IF(ROWS($1:1)>COUNT(B:B),"",SMALL(B:B,ROWS($1:1)))
Copy C3 down to col B's filled extent, to return the required results (ie
the missing nums) neatly packed at the top
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[T. Valko]

I really like the IFERROR function in 2007.
--ron

Yeah, it can really cut down on "uglyness" when dealing with those long
complex formulas.

I'm not convinced that it's *always* more efficient, though.

For *anticipated* errors...

Consider your example:

=IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order)
&":"&MAX(Order))),""),ROWS($1:1)),"")

The *entire* LARGE function has to calculate to generate an error that the
IFERROR can then trap.

If you were to use my suggested error trap, only that portion of the formula
has to calculate to trap the *anticipated* error.

However, when there is no anticipated error generated your formula doesn't
have to calculate a separate error trap.

Overall, I think there are times when IFERROR is not the most efficient
method but this depends on the application and whether or not efficiency is
a high priority.

On a related side note: We've all assumed that the start and end of the
sequence are present in the range. If they might be among the missing
numbers it'd be a good idea to let the user set the boundaries in a couple
of cells (unless they want to hard code it).

 

[Trekman]

Thank you, xlm.

by using your formula I got below missing numbers.  could you please review
and advise.

Order # Missing Numbers
1325    1326
1326    1327
1328    1329
1329    1330
1331    1332
1332    1333
1334    1335
1335    1336
1336    1337
1337    1338

Thank you,
Beth

Beth,

here is what I get, I have shown the formula to the right of the cell
where it is needed.

1325 =IF(A2+1=A3,"",A2+1)
1326 1327
1328
1329 1330
1331
1332 1333
1334
1335 1336

It really does not matter if you start the formula in B2 or B3, it
will only vary the cell where the missing number appears.

-TrekMan