Hi all, first post here, looking to get some advice on what options my organization has for a solution to our problem. Let first preface this by saying it is rather advanced, and I need an Excel Wizard for this. Here is the problem:

I work for a library that is part of a very large (150-200 libraries) consortium of libraries that loans and shares their collections among one another, and we use Excel to track our circulation and keep a database of not only our own libraries item codes, but also the codes of all other libraries in the consortium. As an example, the code for a certain libraries dvds might be abadvd. Another might be hccdvd. Each library has anywhere from 6-10 to 20 or so unique codes that identify what the item is, and what library it belongs to.

We download a monthly report that has all of our speific libraries circulation data (our items that we circulated and also other libraries items that we circulated on loan). Part of what we report to our board is the types of things that got circulated so that they can tell what is popular and what is not - what we should keep purchasing more of (dvds or books or cds for example) and what is not popular and a waste of money for our community.

So say we want to see how many DVDs we circulated in a given month, and we have the report with all of our circulation information in it that is gathered automatically from our self check out system. This report contains data for ALL types of items circulated from ALL libraries that was put in a request for and circulated. To see what is popular, we need to sort by DVD, or CD, or Book, etc. The problem is that there are literally about 5000 or more codes total, as many as 1000 of which might pertain to one particular type of item (say a DVD) which then in turn may have tens of thousands of that item type. Each library has their own way of coding their items, and these updated codes get sent to the consortium monthly. We then download the new set of codes, and we have a selection code method that compares the new set of codes versus our own, and we then add the new codes to our own database collection.

So, when we download our circulation report, we have a VBA file installed that allows us to autofilter the data by it's collection code. In a nutshell, what this VBA script does is look at our codes file, find all items of a certain type (say DVD for example) and then spit out a list of those codes. The circulation report sheet then filters the current data by the code list generated by the VBA script, showing only those items which match the criteria we are looking for. The problem that I am having is that we know for fact that this method will not work with excel 2010, and it is also a bit unreliable, and I have had trouble migrating the system to newer non windows xp computers.

What I am looking for are suggestions of different ways of doing this (maybe setting up a database in Access?) to "future proof" our filtering methods. We used to filter this data by hand every month when our collection was smaller and we circulated less items, but even then it took 2-4 people a few weeks working 8 hour days to get it done. Ideally, these numbers are something that we should be able to get in a few minutes using excel or access.