MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to find missing numbers


Posted by Homero on June 26, 2001 6:54 PM

Please if anybody can helpme on this: I have numbers on column A, 122,123,125,130, and I have in column D: 126,127,129, and I need to put in any cell a macro, to know what numbers are missing? In this formula I can put ( because I know) the small number (122) and the high number (130) and then click on a macro button and when the macro runs showme the numbers what are missing (in this example : 124 , 128)
thanks for all


Posted by Kevin James on June 26, 2001 9:48 PM

Working smart, not hard

Hi Homero,

Yes, that can be written in a macro, but why work so hard.

You can write a formula that merely says:
If Number is greater than Above+1, then "skipped number" else ""

For example
A1 contains 125
A2 contains 127

In B2 enter the formula:
=if(A2>(A1+1),"skipped number","")

Copy that down for however many numbers in have in column A.

Kevin

Posted by Homero on June 27, 2001 10:24 AM

Re: Working smart, not hard

Posted by Homero on June 27, 2001 10:27 AM

Re: Working smart, not hard

ok but I have this two columns with a lot of entrys ( I have in just only one column 950 numbers of invoices, and in the other column 560 entrys) and I cant check one by one if I miss to put any number of invoice.
thanks
Homer