Trying to modify a complex formula that outputs a spilled result in multiple columns

Hi everyone!

I'm working with a complex formula and trying to make some changes. See below for the formula. The formula retrieves data from an extensive drug database (20K+ lines). It then outputs a spilled result into three separate columns, which are ordered by the drug strength, pharmaceutical form, and DIN (drug identification number). See the screenshot below. I also attached some sample data with the formula.

Screenshot.png

I did not write this formula, so it is beyond my understanding. I'm trying to modify the formula so that it spills the 4th column for the route of administration and for this column to come after "Dosage Form" and before the "DIN/PDIN/NPN" column. The drug database I'm using contains both marketed and approved drugs. I'm also trying to get the formula to separate DINs into different columns depending on whether the product is marketed or approved. The end result would be the formula spilling out five columns. I hope what I'm asking makes sense. Let me know if you require additional clarification! I appreciate any help you can provide.

Please Login or Register  to view this content.

"fairly complex" is a bit of an understatement. It >>looks<< totally over the top!

Is there always only ONE code in column B... or can there be multiple codes?

Your sample is oversimplified if that's the case.

You should have, yes. Please provide one that is realistic.

And the expected results, calculated manually are... where?

OK. 99% of the way there.

My output is 2 lines longer than yours. In your formula, IV and IM preparations at the same dose are combined in ONE line. In mine, there are two.

IF you want that to be addressed are there other posssible combinations that should also be concatenated (e.g. SC and IM)?

Formula:

Please Login or Register  to view this content.

Hi Glenn!

Thank you so much for providing me with the formula with the sample. Your formula uses the entire ATC column from the DPD table. I was wondering how I would go about using it for specific drugs. Ex. If I want the formula to generate a result for all drugs that contain cyproheptadine as the only ingredient (number of AIS = 1). This was why I included

Please Login or Register  to view this content.

, with cell A5 containing the name of the drug, in the previous formula.

I glanced at the entirety of the data I'm working with... I found quite a bit of route of administration combinations for the same drug and dose. I listed some below, but there's a good chance that I've missed some. Could you show me how you would concatenate these route of administration combinations into your formula?

- Inhalation, Oral
- Intramuscular, Intravenous
- Intramuscular, Intratracheal, Intravenous, Subcutaneous
- Intramuscular, Subcutaneous
- Intravenous, Subcutaneous
- Intramuscular, Topical
- Ophthalmic, Otic
- Otic, Topical
- Intramuscular, Intravenous, Subcutaneous
- Intrathecal, Intravenous, Subcutaneous
- Oral, Topical
- Intra-Articular, Intralesional, Intramuscular, Intrasynovial
- Intramuscular, Intrasynovial
- Intralesional, Intramuscular, Intrasynovial
- Intramuscular, Intrasynovial, Intravenous
- Intraperitoneal, Irrigation
- Rectal, Topical
- Block/Infiltration, Intra-Articular, Epidural
- Block/Infiltration, Epidural
- Block/Infiltration, Intramuscular, Subcutaneous, Epidural
- Intraperitoneal, Intravenous, Subcutaneous
- Intra-Articular, Intramuscular, Intravenous
- Intramuscular, Intraperitoneal, Intravenous, Subcutaneous
- Intracardiac, Intrapulmonary, Intravenous
- Intracardiac, Intraperitoneal, Intrapulmonary, Intravenous
- Intracardiac, Intravenous
- Intracardiac, Intramuscular, Intratracheal, Intravenous, Subcutaneous
- Intravenous, Subcutaneous, Epidural
- Intramuscular, Subcutaneous, Epidural
- Intra-Articular, Intramuscular
- Intra-Articular, Intrabursal, Intradermal, Intrasynovial
- Intra-Articular, Periarticular, Intrabursal, Intrasynovial
- Intra-Articular, Intrabursal, Intradermal, Intramuscular
- Intra-Articular, Intralesional, Intramuscular, Intravenous, Soft Tissue Injection
- Intra-Articular, Periarticular, Intrasynovial
- Intradermal, Subcutaneous
- Intradermal, Inhalation, Intravenous, Oral
- Topical, Vaginal
- Oral, Rectal
- Intra-Arterial, Intravenous
- Intra-Arterial, Intramuscular, Intravenous
- Intradermal, Subcutaneous, Percutaneous
- Intracerebroventricular, Intra-Arterial, Intramuscular, Intrathecal, Intravenous
- Intra-Arterial, Intramuscular, Intrathecal, Intravenous
- Intraperitoneal, Irrigation, Topical
- Intracavitary, Parenteral (unspecified)
- Intraperitoneal, Intravenous
- Intraperitoneal, Intravenous, Oral
- Inhalation, Intravenous
- Teat-Dip, Topical
- Intravenous, Intravesical
- Subarachnoidal, Intravascular
- Intra-Arterial, Intracavitary, Intramuscular, Intravenous, Subcutaneous
- Intracutaneous, Subcutaneous
- Intrauterine, Vaginal
- Intramuscular, Intrauterine, Subcutaneous
- Intramuscular, Intravenous, Epidural
- Intramuscular, Intravenous, Subcutaneous, Intraperitoneal
- Intravenous, Epidural
- Intravenous, Oral
- Retrobulbar, Epidural
- Intracaudal, Epidural
- Nasogastric, Oral
- Intravenous, Haemodialysis
- Inhalation, Intravenous, Oral
- Nasal, Oral
- Intramuscular, Intrapleural, Intravenous, Subcutaneous
- Intraglandular, Intramuscular
- Instillation, Intravenous, Oral
- Buccal, Sublingual
- Buccal, Oral
- Infiltration, Epidural
- Topical, Udder Wash
- Interstitial, Intravenous
- Nerve Block, Epidural

OMG!

Plan B went out the window when I saw that...

Plan C took a while, as I had to double back and start over again, with some relatively unfamiliar stuff. However, here it is. Points:

1. A added a cell in the first row to select the AIC code. The table will update.

2. I duplicated the diphenhydramine data, changed ATC to chlorpheniramine and the AIC to (randomly) 188.

It looks good to me. Check it over.

I should have added. Look at the yellow cells in the raw data. They are condensed into TWO rows (as they were in your original formula) as the pharmaceutical form in 2 of them was "liquid" and in the other was "solution". I decided not to fiddle with that...

Anything else??

As a result of going down some very long dead-ends, I've spent a LOT of time looking at this already. So I will not adjust more stuff blindly.

Show me.

1. Show me in a refreshed sample sheet where the use of AIC 182 does not work. Show me, in the same sheet why solely using the name diphenhydramine would also not work. What about preparations that contain more than one active principal? If they complicate things, include examples and show CLEARLY how they should be handled.


2. The DINs are separated by carriage returns (see my sheet). I thought it was cleaner looking. If they are one long string in yours, enable text wrapping. It's easy to change to comma-space but check BOTH and tell me what you want.

3. Padding DINs, easy.

4. On the revised sample sheet, include a table showing (for - at least - ALL the routes of administration used in the sample sheet), with one route per line, the exact order in which you want them to appear.

V3. I think that's it all covered.

Hi Glenn! The formula looks good and is exactly what I need... however, I tried transferring it to my main spreadsheet, and it just froze up my computer. I'm wondering if because it's trying to sift through 38K rows...

That's a lot of rows! The obvious suggestion is to split the initial search to return the drug into a separate table on a separate sheet (which can be hidden, if desired) and then do all the fancy stuff usng formulae which don't attempt to store ALL 17K rows. See amended file.

Attachment removed by GK. Slight problem, which I'll look at later...

Scrap that. The wee ***** have unsorted. Away for a while.

Easy fix. I had accidentally deleted "Oral" from the list of routes...