Hello,
I am working on a decision-making tool which will help doctors choose an appropriate drug given a patient’s symptoms and the medications he is currently taking. Cell E9 (labeled “FirstChoice”) will give the doctor treatment instructions which includes a drug category (or categories) from which to choose a drug. Cell F9 (labeled “ProductsFirst”) then will look for the drug category (or categories) in E9 and output the corresponding brand name drug product(s).
Cell E10 (“Alternative”) will give an optional drug category choice and also pull the brand name into F10 (the same concept as “FirstChoice”).
The drug categories and products are:
Category Product
SAMA Atrovent
LAMA Spiriva
SABA Proair
LABA Serevent
ICS Aerobid
SAMA+SABA Combivent
LAMA+LABA Anoro
ICS+LABA Advair
I started thinking that I could do this within Excel using text functions (e.g., FIND, SEARCH, MID, LEN, etc.) and a VLOOKUP array, or INDEX-MATCH but quickly realized that that was becoming overwhelming and cumbersome and that VBA would be more efficient. I now think I need to use VBA functions such as FOR EACH, LOOP, FIND NEXT, etc. but having never written such a complicated loop, find myself at a loss as to where to even start.
I’ve attached a partial copy of the workbook. I deleted non-relevant worksheets so some macros will not work. The sheet of interest is “Recommendation.” I manually entered outputs into cells E9, F9, E10, and F10 as examples.
Many thanks in advance for your expertise. You all are the best!
Bookmarks