The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2011, 10:14 PM
Jaguars! Jaguars! is offline
Guest
 
Join Date: Nov 2010
(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!
Reply With Quote
Advertisements  
  #2  
Old 11-27-2011, 10:39 PM
aceplace57 aceplace57 is offline
Guest
 
Join Date: Oct 2009
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..
Reply With Quote
  #3  
Old 11-27-2011, 11:01 PM
jjimm jjimm is offline
Guest
 
Join Date: Jul 2001
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.
Reply With Quote
  #4  
Old 11-28-2011, 12:00 AM
tomndebb tomndebb is offline
Mod Rocker
Moderator
 
Join Date: Mar 1999
Location: N E Ohio
Posts: 36,484
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.
Reply With Quote
  #5  
Old 11-28-2011, 12:29 AM
Quartz Quartz is offline
Charter Member
 
Join Date: Jan 2003
Location: Home of the haggis
Posts: 20,056
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.
Reply With Quote
  #6  
Old 11-28-2011, 03:06 AM
Hunter Hawk Hunter Hawk is offline
Guest
 
Join Date: Nov 2003
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.
Reply With Quote
  #7  
Old 11-28-2011, 07:27 AM
FasterThanMeerkats FasterThanMeerkats is offline
Guest
 
Join Date: Sep 2010
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.
Reply With Quote
  #8  
Old 11-28-2011, 07:30 AM
jjimm jjimm is offline
Guest
 
Join Date: Jul 2001
FasterThanMeerkats, I like your thinking. Much simpler than anything else yet suggested.
Reply With Quote
  #9  
Old 11-28-2011, 10:52 AM
tomndebb tomndebb is offline
Mod Rocker
Moderator
 
Join Date: Mar 1999
Location: N E Ohio
Posts: 36,484
I agree. CountIF is the way to go.
Reply With Quote
  #10  
Old 11-28-2011, 11:08 AM
Buchanan Buchanan is offline
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!
Reply With Quote
  #11  
Old 11-28-2011, 11:22 AM
ultrafilter ultrafilter is offline
Guest
 
Join Date: May 2001
Quote:
Originally Posted by Hunter Hawk View Post
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.
Reply With Quote
  #12  
Old 11-28-2011, 07:04 PM
jjimm jjimm is offline
Guest
 
Join Date: Jul 2001
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?
Reply With Quote
  #13  
Old 11-28-2011, 10:18 PM
Jaguars! Jaguars! is offline
Guest
 
Join Date: Nov 2010
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..
Reply With Quote
  #14  
Old 11-29-2011, 07:03 PM
jjimm jjimm is offline
Guest
 
Join Date: Jul 2001
Quote:
Originally Posted by Jaguars! View Post
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..
Reply With Quote
  #15  
Old 12-20-2011, 09:44 PM
Jaguars! Jaguars! is offline
Guest
 
Join Date: Nov 2010
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!
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

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 Jump


All times are GMT -5. The time now is 05:29 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

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

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.