I Know What I Know.. But How Do I Know What I Don't Know?March 24, 2011 8:12 PM   Subscribe

Excel Filter: I have a long list of file names in numerical order in an Excel spreadsheet. I need to know what numbers are missing, so I can track down the related files. Is there a quick and (relatively) painless way of doing this without having to sift through all the numbers manually?
posted by Philby to Computers & Internet (5 answers total) 3 users marked this as a favorite

It is not quite clear what the question is, but if I understand it correctly, you may be able to accomplish what you want by comparing your list to the numbers on the left axis. If you go to cell A20 and the file name is 18boxes.pdf, then you know you are missing two at that point.

If you need to convert text to numbers, so that 111 comes after 12, you could copy the list to a text file, do a manual search to replace each instance of a letter with a tab plus the letter, and copy back. This is tedious but it results in the numbers in one column then the letters in the next. Then convert the text "numbers" in the first column to real numbers.
posted by yclipse at 8:33 PM on March 24, 2011

Can you give us an example please, it would help.
posted by TheBones at 8:33 PM on March 24, 2011

Best answer: cCreate another list with you ideal (is complete) list of possible numbers. Now (assuming data is in column a, and ideal list is in column c) in d1 put:

=match(c1,a:a,0)

and fill down
posted by pompomtom at 8:37 PM on March 24, 2011

Best answer: Somewhat quick and painless, if you don't get a better answer. It's hard to say exactly without seeing your data, but this will work, possibly with a few changes.

1) Sort the files. I'm assuming they are in column 'A'. If Column A doesn't have only the numbers (say it has the extensions in it) you'll need to create a column of just the numbers using the string functions (left, right, indexof, etc)

2) Create another column (call it 'B') with continuous numbers from 1 to the highest number you want to look for (make sell B1 '1', cell B2 '=B1 + 1', and copy B2 down until you get the highest number you want to check for.

3) put the following formula in cell C1: =IFERROR(VLOOKUP(B1,A:A,1,FALSE)>=1,"MISSING")

4) copy that formula to every cell in C next to the numbers in B

You should see that word 'MISSING' next to every number that doesn't have a corresponding file. You can sort columns C and B together and see all the missing files.

[On preview this is basically a longer version of pompomtom's answer]
posted by Notorious S.O.C.K at 8:38 PM on March 24, 2011

Response by poster: Sorry my question wasn't as clear as it could have been.

pompomtom and Notorious have it in the bag. Thanks very much, guys- worked out lovely-ly.
posted by Philby at 9:02 PM on March 24, 2011

« Older Everyone breaks but the bass keeps playing as if...   |   A scanner for art Newer »