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?
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
=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
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
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
This thread is closed to new comments.
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