Invoice Numbers

Posted by Al Polan on August 10, 2001 6:57 PM

I have in column A about 20,000 rows of Invoice numbers, I need a formula that will let me know which Invoice numbers are missing. Thank you in Advance.

Posted by Cory on August 10, 2001 11:19 PM

This will take a couple of steps, but I can't think of anything easier short of a macro:

Fill in column B with a complete list of invoice numbers (to have something excel can compare the not-so-complete list to).

In colummn C, cell C1 enter this formula:

=if(isna(Vlookup(A1,$B1:$B30000,1,false)),"",Vlookup(A1,$B1:$B30000,1,false))
Now copy this down as far as your partial list goes.

Next you can use an autofilter (Data --> Filter --> AutoFilter) with the option of 'blanks' to find the missing invoices in Column A.

Last, just copy and paste the filtered column A to wherever you'd like to. Maybe the next sheet where you can print it without corrupting the original data...

That at least get you started?

Cory