How to search multiple files?

May 4, 2007 10:24 AM

I have a list of several thousand serial numbers in an excel file. Each of these serial numbers is located in another excel file in a seperate folder on my computer. Is there any program/method of finding which file each serial number was originally in?

Is there an easy way to do this without typing each serial number into the windows search feature? For example, my list might say:

Serial1
Serial2
Serial3

I would like to know:

Serial1 - spreadsheet4.xls
Serial2 - spreadsheet7.xls
Serial3 - spreadsheet2.xls

posted by Ramithorn to computers & internet (9 comments total)

Is the windows search feature still too slow if you just limit it to the folder where the spreadsheets you want to search are?

I'm not sure if there's a tool that does what you're after, so the only other thought I have that might speed things up is converting the files to CSV format and using a grep-like equivalent for windows on them.
posted by twiggy at 10:36 AM on May 4, 2007

Is the serial number in the same cell or some sort of predictable place in all the spreadsheets?
posted by zixyer at 10:45 AM on May 4, 2007

The serial number will always be in the "A" column. It's row could be anywhere.
posted by Ramithorn at 10:49 AM on May 4, 2007

Pull the excel sheets into Access as three different tables and write a query?
posted by mrbugsentry at 10:56 AM on May 4, 2007

I'm sure there is a better way using Excel's scripting but you could likely get what you need using Cygwin and Textpad.

grep -ril "serialnumber" *

will return the file name of the file that contains the serial number

You could use something like a combo of TextPad to take that column and turn it into a series of cygwin commands using regep.

e.g.

replace ^ with grep -ril
and $ with " *
posted by bottlebrushtree at 11:01 AM on May 4, 2007

I was thinking, merge the three spreadsheets with an indicator variable that indicates which file each record came from (this can be accomplished by adding a new column pre-merge in each file). Then you can get the answer just by searching the single, merged file.
posted by grobstein at 11:24 AM on May 4, 2007

To clarify, there are around 70 excel files that I'm searching through. Also, not every serial number will be found.
posted by Ramithorn at 11:39 AM on May 4, 2007

If I understand you correctly, I would use vlookup (not saying this is the best or only way, but it's what I would be able to do in your position - I do this sort of thing nearly every day using this method):

- make additional columns in the Serial # spreadsheet, one for each of the other Excel files (Title them with the name of each spreadsheet)
- use vlookup. In case you don't know, you'd write the formula like this =VLOOKUP(A1,'[Other Serial Number Spreadsheet.xls]Specific Worksheet on Other Serial Number Spreadsheet'!$A$3:$A$1000000,1,0) You can do it by typing =VLOOKUP( then clicking on the first cell containing a serial number you want to match, then putting a comma, then going to the first lookup spreadsheet, selecting the entire column, then comma, then 1 (because you want it to bring you the result from the first column in the range selected), then 0 (because you want an exact match), then ) (close parenthesis). Repeat this for each column/lookup spreadsheet (if the spreadsheets are named consistently, you can just copy + paste the formula and then change the titles as needed).
- as long as you are careful to put the $ in, you can just do this for the first row, then copy the formulas all the way down the page (hover the mouse pointer at the bottom right corner of the top cell until you see a bold +, then double click and it will fill in all the way down).

Sorry if this is too basic or you've already tried it but it seems to me that it should work.
posted by biscotti at 11:50 AM on May 4, 2007 [1 favorite]

Or you can just pick "insert/formula" and pick vlookup and follow along with the instructions, just remember to go back and put the $ in so you can use the one formula for the whole spreadsheet.
posted by biscotti at 11:52 AM on May 4, 2007