Remember Me?

 Straight Dope Message Board Remember Me?

#1
11-27-2011, 10:14 PM
 Jaguars! Guest Join Date: Nov 2010 Location: NZ Posts: 312
(Excel) Finding missing numbers in a list

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.

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
11-27-2011, 10:39 PM
 aceplace57 Guest Join Date: Oct 2009 Location: CentralArkansas Posts: 23,157
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.

Last edited by aceplace57; 11-27-2011 at 10:43 PM.
#3
11-27-2011, 11:01 PM
 jjimm Guest Join Date: Jul 2001 Posts: 28,704
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
11-28-2011, 12:00 AM
 tomndebb Mod Rocker Moderator Join Date: Mar 1999 Location: N E Ohio Posts: 40,483
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
11-28-2011, 12:29 AM
 Quartz Charter Member Join Date: Jan 2003 Location: Home of the haggis Posts: 27,839
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
11-28-2011, 03:06 AM
 Hunter Hawk Guest Join Date: Nov 2003 Location: Pacific Northwest Posts: 3,615
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
11-28-2011, 07:27 AM
 FasterThanMeerkats Guest Join Date: Sep 2010 Posts: 481
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 (C), 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 (C) that's not a 1.
#8
11-28-2011, 07:30 AM
 jjimm Guest Join Date: Jul 2001 Posts: 28,704
FasterThanMeerkats, I like your thinking. Much simpler than anything else yet suggested.
#9
11-28-2011, 10:52 AM
 tomndebb Mod Rocker Moderator Join Date: Mar 1999 Location: N E Ohio Posts: 40,483
I agree. CountIF is the way to go.
#10
11-28-2011, 11:08 AM
 Buchanan BANNED Join Date: Oct 2011 Posts: 73
This forum has some really solid Excel users. I thought I was good with my VLOOKUPs and pivot tables but I thought wrong!
#11
11-28-2011, 11:22 AM
 ultrafilter Guest Join Date: May 2001 Location: In another castle Posts: 18,988
Quote:
 Originally Posted by Hunter Hawk 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.
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
11-28-2011, 07:04 PM
 jjimm Guest Join Date: Jul 2001 Posts: 28,704
Oops, I made an error in my (now redundant) explanation:

Does number 1 exist in the whole of column B?

Does number 1 exist in the whole of column A?
#13
11-28-2011, 10:18 PM
 Jaguars! Guest Join Date: Nov 2010 Location: NZ Posts: 312
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. 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.

Last edited by Jaguars!; 11-28-2011 at 10:19 PM.
#14
11-29-2011, 07:03 PM
 jjimm Guest Join Date: Jul 2001 Posts: 28,704
Quote:
 Originally Posted by Jaguars! I'm sure one (and probably all) will work when I get time for trying them.
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.

Last edited by jjimm; 11-29-2011 at 07:04 PM.
#15
12-20-2011, 09:44 PM
 Jaguars! Guest Join Date: Nov 2010 Location: NZ Posts: 312
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!

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     General Questions     Great Debates     Elections     Cafe Society     The Game Room     Thread Games     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit

All times are GMT -5. The time now is 03:45 PM.

 -- Straight Dope v3.7.3 -- Sultantheme's Responsive vB3-blue Contact Us - Straight Dope Homepage - Archive - Top

Send questions for Cecil Adams to: cecil@straightdope.com