Drug costs

I'm working on a project involving a health economic evaluation of a clinical trial. The trial collected data on drugs prescribed for each patient over the course of, let's say, a year. My job is to assign a cost for each row in a very large Excel spreadsheet (each of which represents one prescription). The data I have are sometimes incomplete, but in general, I have: Drug Dose (a number), Drug Unit (eg, milligrams), Drug Frequency (free text), Drug Route (oral, topical, intramuscular, etc.), and Drug Name (free text).

The process involves a cumbersome program called Red Book for Windows, which allows me to look up the drugs by name or by National Drug Code (NDC) -- unfortunately, I don't have the NDC, so I have to look them up by name. The first problem is that many of the drug names don't match. Let's take, for example, ciprofloxacin, a generic antibiotic often referred to as cipro.  Cipro is sold in dozens of forms (pills, eyedrops, eardrops, etc.) in dozens of strengths by dozens of manufacturers. It's listed in Red Book in lots of different ways, including: CIPRO, CIPROFLOXACIN, ciprofloxacin, ciprofloxacin/ciprofloxacin hydrochloride, ciprofloxacin HCL, CIPROFLOXACIN HYDROCHLORIDE, ciprofloxacin hydrochloride, ciprofloxacin hydrochloride/dexamethasone, ciprofloxacin hydrochloride/hydrocortisone, CIPROFLOXACIN IN DEXTROSE, CIPRO HC, CIPRO IV, and CIPRO XR.

The cost of a 500mg tablet may be listed under any of these, and I have to locate the lowest cost available (because we're using a conservative approach to costing - rather than use an average, we use the lowest cost in order to underestimate the potential burden or cost of the illness). Then I can apply that cost to all the instances where it shows up in my spreadsheet. Then I have to go find the lowest cost for 200mg delivered intravenously, and so on.

So this fun project has been eating up lots of my time lately. It's not exactly brain surgery, but it can't be automated and can't be outsourced - and it has to be done very carefully. It would go a lot faster if the Red Book program were easier to query, but, in my opinion, it's not a user-friendly application at all!