Finding Missing numbers in the attached list

[vinnys]

Hi all,

I'm trying to find missing check numbers from the following (attached) list. After trying without success with several formulas I have decided to call for help.

I am trying to obtain a numerical list of the missing numbers from the list.

Many Thanks

Vinny

[Tony Valko]

I didn't look at your file...

See if this helps.

Data Range

	A	B	C
1	Numbers	-----	Missing
2	2		1
3	3		4
4	5		6
5	8		7
6			9
7			10
8

We have a number sequence from 1 to 10. Some of these numbers are entered in column A. We want to find what numbers are missing from that sequence.

I see you're using Excel 2002 so this formula is written for that version of Excel.

Enter this array formula** in C2 and copy down until you get blanks:

=IF(ISERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:10")),A$2:A$10,0)),ROW(INDIRECT("1:10"))),ROWS(C$2:C2))),"",SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:10")),A$2:A$10,0)),ROW(INDIRECT("1:10"))),ROWS(C$2:C2)))

** 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.

Note that this formula will be very slow to calculate on large number sequences and large data ranges.

[samba_ravi]

see the attached file
is this not working

[vinnys]

Hi all,
Apologies for being so thick but even with your help I'm still getting either weird errors or blank results.

The check numbers range from number 56708 to number 67508
And from rows A2 to A7649

I have attached my spreadsheet so far given the help of the formula above in the hope that somoene can help.

Big thanks everyone.

Vinny

[RobertMika]

It would be esier if you use VBA:
http://www.extendoffice.com/document...-sequence.html
Tony's formula will do the job but will take lot of resources.

[Tony Valko]

If you're using Excel 2007 or later then we can make the formula a bit more efficient but it will still be slow to calculate.

This array formula** entered in C2 and copied down until you get blanks:

=IFERROR(SMALL(IF(ISNA(MATCH(ROW($56708:$67508),A$2:A$7649,0)),ROW($56708:$67508)),ROWS(C$2:C2)),"")

** 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.

The formula will not work correctly if you insert new rows. It's a trade-off between being less robust or more efficient.

Since the data range is kind of large and the check sequence is also kind of wide I think you'd be better off if you can find a macro to do this.

[MarvinP]

Hi Vinny and welcome to the forum,

I did this problem by first sorting your check numbers from lot to high. Then I created a Helper Column that simply subtracted adjacent check numbers. Most of these are equal to 1 as they are next to each other. If it is greater than one then you've skipped a number or two or ....

I decided to do an Advanced Filter to pull out all those check numbers that had a skip just in front of them. See if this example helps...

[Tony Valko]

Here is a macro I have that will do this.

I'm kind of hesitant to post VBA code because I'm not much of a programmer so if you need to change this very much I may not be able to figure it out!

I ran the macro in your posted sample file and it took about 1 second to generate the list of 3373 missing check numbers.

Please Login or Register  to view this content.

[samba_ravi]

find the the attachement

[Armando Montes]

Look here:
http://www.ozgrid.com/forum/showthread.php?t=156171

[Ghozi Alkatiri]

array formula
=SMALL(IF(ISNUMBER(MATCH(ROW($56768:$67508),$A$2:$A$7649,0)),"",ROW($56768:$67508)),ROW(1:1))

copy down