Find missing numbers in a list

[Guest]

I have a list of numbers with many missing .
ie 1,2,5,6,9,10, up to 999
Need to have a list of the missing numbers
ie 3, 4, 7, 8 etc
All help welcome.

 

[Rick Rothstein \(MVP - VB\)]

I have a list of numbers with many missing .

ie 1,2,5,6,9,10, up to 999
Need to have a list of the missing numbers
ie 3, 4, 7, 8 etc
All help welcome.

In what format do you have these numbers? A String? Filled in cells? What
type of solution are you looking for... a VBA macro?

Rick

 

[Guest]

Hi Rick

In what format do you have these numbers? A String? Filled in cells? What
type of solution are you looking for... a VBA macro?

My numbers are in a coloum of cells.
Easy answer would be another coloum of cells for the missing numbers.
Does this help ?

 

[Guest]

You need 3 columns.

Column A you list of numbers with numbers missing
Column B a full list of the numbers i.e. 1 to 999 with no gaps

In C1 enter the formuls

=COUNTIF(A$1:A$5,B$1:B$999) and drag down to the length of column B

Numbers with a 0 against them are missing from List A.

Mike

 

[Rick Rothstein \(MVP - VB\)]

You need 3 columns.

Column A you list of numbers with numbers missing
Column B a full list of the numbers i.e. 1 to 999 with no gaps

In C1 enter the formuls

=COUNTIF(A$1:A$5,B$1:B$999) and drag down to the length of column B

Numbers with a 0 against them are missing from List A.

I like that solution. One suggestion to maybe make it easier to spot the
missing numbers. If you use this variation on your formula...

=IF(COUNTIF(A$1:A$5,B$1:B$999)=0,"X","")

the missing numbers will have an "X" next to them (the non-missing numbers
will have a blank next to them).

Rick

 

[Guest]

a bit of a variation. This extracts the missing numbers,

=IF(COUNTIF(A$1:A$5,B$1:B$100)=1,"",B1)

Once again in C1 and drag down

Mike

 

[Guest]

Thanks for that, I didn't like the difficulty you note either so I had just
posted another that extracts the numbers

Mike

 

[Leo Heuser]

I have a list of numbers with many missing .
ie 1,2,5,6,9,10, up to 999
Need to have a list of the missing numbers
ie 3, 4, 7, 8 etc
All help welcome.

Hi Contar

Assuming list in A2:A1000, here's one way to do it:

1. In B2 enter this array formula as one line (B1 must be present and
must not contain
a number found in column A)

=MIN(IF((COUNTIF($A$2:$A$1000,ROW(INDIRECT("1:999")))=0)*
(COUNTIF($B$1:B1,ROW(INDIRECT("1:999")))=0),ROW(INDIRECT("1:999"))))


2. Confirm with <Shift><Ctrl><Enter> instead of just <Enter>.
3. Copy B2 down with the fill-handle (the little square in the lower
right corner of the cell).

 

[Guest]

Thank you very much guys.

The original did the job, but Mike's extraction is the one I have used.

Thanks again

Contar

 

[T. Valko]

Here's another one:

Assume numbers are in the range A1:A100 (can include text and empty cells,
won't affect the formula)

Enter this array formula** in B1 and copy down until you get errors:

=SMALL(IF(ISNA(MATCH(ROW($1:$999),A$1:A$100,0)),ROW($1:$999)),ROWS($1:1))

With an error trap:

=IF(ROWS($1:1)<=999-COUNT(A$1:A$100),SMALL(IF(ISNA(MATCH(ROW($1:$999),A$1:A$100,0)),ROW($1:$999)),ROWS($1:1)),"")

As my buddy Max would say, returns all missing numbers neatly bunched at the
top. <g>

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)