(Excel) Finding missing numbers in a list

Factual Questions
1

I hate to bother GQ with this type of work question but I don’t even know what sort of search terms I would enter into Google, MS Help, Etc.

What I have is an incomplete list of job numbers my company has done. I want to find out which jobs aren’t in the database. here is an example:
7502
7503
7557
7559
7560
etc.

So I’d like is to have excel return the numbers 7504-7556, 7558, and so on.

My excel knowledge is kinda patchy, but I can do basic IF THEN sorts of formulas. I just don’t know what sorts of commands are available for this sort of task, or what sort of method I might use. There might even be something obvious that I’ve missed. :smiley:

I thought I could go to the bottom of the column, generate a complete list of job numbers (10 000 of them), sort , and kludge together something to eliminate the doubled up numbers. But surely there would be a more elegant solution than this?

BTW, I won’t be able to reply for a while, as I’m going home now. Dopers, you have until tomorrow!

Thanks
Jaguars!

2

Sort by job number

in a if formula subtract A2-A1 > 1 then B1=x

copy that down

Sort on B1 descending

All the job numbers with gaps will be grouped together at the top.

create your list manually using that info.

Not the most elegant, but it’s semi-automated.

I could easily create the list using a real programming language. Excel is always a quick & dirty tool for me. When there isn’t time to do it nice.

3

aceplace57’s solution is good but only returns those values that have gaps between them: not the actual gaps.

So here’s my solution.

I did this using an example, so I’ll post that. Once you know how it works you can extrapolate to your actual spreadsheet.

In column A enter: 1,2,3,5,6,9,10
In column B enter: 1,2,3,4,5,6,7,8,9,10

In cell C1 enter the following formula: =IF(ISERROR(VLOOKUP($B1,$A:$A,1,FALSE)),$B1,"")

Now drag that formula all the way down to the bottom of the numbers in column B (in this case, down to C10).

What this is doing is saying: check no. 1. Does number 1 exist in the whole of column B? If it does, there is no error so ignore the rest of the formula.

When we get to no. 4 in column B, however, there is no 4 in column A and an error is generated. The rest of the IF statement kicks in - “if there is an error in this lookup, then use the value that should be there” - which it takes from column B.

Now copy column C elsewhere, right-click and choose “Paste special…” then choose “Values”.

That should give you a quick and dirty list of all missing job numbers.

4

Alternatively:
Let us assume that your range of job numbers is 7500 through 9500.
Copy all the current job numbers into column A of a worksheet and sort it, ascending.
On a second worksheet, create a list of all possible numbers:
Enter the text JOB in A1 and the text FOUND in B1
in A2 enter the lowest possible value, 7500
in A3 enter A2 + 1
Copy A3 down over however many cells you need to include all the possible values, (in this case A2002, i.e. 7500 through 9500 is 2,001 values plus the header in Row 1 gives 2,002).

Now in column B, enter the formula
=VLOOKUP(A1,Sheet1!$A$2:Sheet1!$A$2002,2,FALSE)
on the first row that contains a job number.

Copy that cell down the column to the last job number.
You need the “Sheet1!” to point to the sheet on which the lookup values are stored.
You need the dollar signs to indicate absolute positioning so that the numbers will not increment in each cell into which you copy the formula.

On Worksheet 2, Column B should now contain a list of either job numbers or blanks, (or the value #N/A).
Turn filtering on for Row 1 and filter in only the blanks or #N/A references.
Column A is your list, in ascending order, of your missing job numbers.

5

An alternative:

Sort the spreadsheet by job number. Then, assuming you have the column of numbers in columns A, then in column B, starting in B2, put:

=IF ((A2-A1)=1,"", if ((A2-A1=2, A1+1,TEXT(A1+1)&" - "& TEXT(A2-1))) and copy it down.

This will give you a blank value in columb B in the case of consecutive job numbers, the missing job number if the gap is 2, and the missing range of job numbers if the gap is more than 2.

Now filter out the rows where column B is blank.

6

Since you mention that you have a database, is there a reason why you have to use Excel rather than just use the database? Because you’re basically looking at a query like (warning! pseudocode!) “SELECT JobId FROM TblReferenceNumbers JOIN TblCompletedJobs WHERE TblReferenceNumbers.JobId NOT IN TblCompletedJobs.JobId ORDER BY TblReferenceNumbers.JobId”

If you have a friendly DBA, this should be a pretty simple job.

7

Use the countIF function.

-Have your data in one column (A)

-In another column (B) create a sequential list of the numbers you want to check. (1,2,3…)

-In the next column ©, use countIF functions, to check your original dataset (A) and count the number of times the numbers in column (B) appear.

-Sort or filter on anything in © that’s not a 1.

8

FasterThanMeerkats, I like your thinking. Much simpler than anything else yet suggested.

9

I agree. CountIF is the way to go.

10

This forum has some really solid Excel users. I thought I was good with my VLOOKUPs and pivot tables but I thought wrong!

11

Exactly. This is a pretty trivial database operation known as a right join. There’s no cleverness required whatsoever–Access even has a wizard that’ll set it up for you.

12

Oops, I made an error in my (now redundant) explanation:

Does number 1 exist in the whole of column B?

should read:

Does number 1 exist in the whole of column A?

13

OK, so this little project got sidelined today. I spent the time planned for it gently turning an earthwork contractor round to my way of thinking- that a house on a hillside would indeed require a flat platform to be cut (and could not be skipped just because the landowner said that the house was ‘on natural ground.’) and that the plan marked **‘proposed earthworks’ **showed the proposed earthworks and was not an incorrect plan of the original ground.:smack: It was not entirely straightforward.

Anyhow, that’s a way better set of replies than I thought I’d get; I’m sure one (and probably all) will work when I get time for trying them.

The Database I got them from was an .MDB file for a custom layer on our GIS program. I know nothing at all about acess, I had to bluff it just to get the info into excel. And no, I don’t have any friendly DBAs (Database Administrators?), just a SNAFU small business type of setup.

14

Unless you want to upskill yourself in new Excel techniques, don’t bother with anyone’s solution apart from Meerkats’s.

ETA: a meerkat helping a jaguar, who would have thought.

15

Hi Again - You guys were very helpful and I never acknowledged it!

I made my list (experimenting with a few of these methods) and now when there is extra downtime I kill an hour entering jobs into quickmap. I thought about using access to enter them into qmap but decided that the time to self teach and the risk of mucking things up wasn’t worth the time savings. I’m going to go back and refresh how these formulae work when I’m finished because I’m always up for upskilling!