In such case (i.e. 1000's of source items, from which up to 500 must be extracted) I'd go with dgorsman's idea. BTW, what you're describing sounds a lot like KeyNoting in my industry. Do you stick with the same code number in the BOM? I.e. if code 543 is to be placed in the BOM, does it stay 543 or does it get renumbered per drawing to the next increment as selected?
Now, to try and figure out what dbms to use. Some are free, some are fast in some cases, nearly all would be faster and more robust than Excel. Let's take the simplest and cheapest one out there: dBase files. These are simply dBf files with some indexing to speed up searches. A more expensive (and also more comprehensive) one would be to use M$ Access MDB databases - but I'd only recommend those if you also want several tables of different types of data interrelating with each other and also want to create saved queries, input forms and output reports. Even then MDB isn't very good if you have more than one person working on the data at any one time. A similar, yet cheaper thing could be to use ODB file (i.e. Open Office Base) - they work a lot similar to MDB, but in my experience Forms & Reports is rather poorly implemented compared to M$ Access. If you need something where 100's of people can add to / modify / extract from such database, you'll have to look into a Client-Server idea (MS SQL / MySQL / PostGre / FireBird / InterBase / etc.) - some are free, some EXTREMELY expensive. Usually you could work on these Client-Server databases through Access or Open Office Base - though you don't need to (it would only be a user interface for such backbone database). Also Excel can export to some of these, and even with some fiddling you could export through an ODBC driver to any of the others. So even if your data is in Excel now - it's not a train smash to convert. You can even revert backwards from the database into Excel, or even create a live link in excel which would show all updates automatically - though with the number you've quoted I'd steer clear of that idea.
There's a quick way of linking to/from lisp to these using a library called
ADOLisp. As long as the database has an ADO or ODBC driver it would be trivial to link to such data store. Most windows installations come standard with at least DBF/MDB ODBC drivers - even if you don't have such program installed. So going that route might be the most broadly based alternative - i.e. you need not install extra programs to use your data.
The point is, the lisp involved would need to be designed to work on whatever you choose. If you stick with Excel it would be totally different from using ADOLisp. Depending on which DB you choose there would even be slight differences while using ADOLisp.
