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.

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

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 ?

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

"contar" wrote:

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.

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

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

"contar" wrote:

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.

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

Mike

"Rick Rothstein (MVP - VB)" wrote:

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

"contar" skrev i en meddelelse
...
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(IN DIRECT("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).



--
Best regards
Leo Heuser

Followup to newsgroup only please.

Thank you very much guys.

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

Thanks again

Contar

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)),RO W($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)

--
Biff
Microsoft Excel MVP


"contar" wrote in message
...
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.