Hello,
I am working on a decision-making tool (using Excel 2013) which will help doctors choose an appropriate drug given a patient’s symptoms and the medications he is currently taking. Cell E9 (labeled “FirstChoice” on sheet 'Recommendation') 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 (labeled "ProductsSecond") using the same above concept.
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
Here is a screen shot of the output as an example:
<tbody>
</tbody>
I initially thought 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.
My son is learning Python programming and he thought that this could be accomplished in that code using the 'dictionary' function. Perhaps there's a similar function/method in VBA?
Full Disclosure: I posted this same request on another help site a couple of weeks ago, but have not received any solutions. Here's hoping that somebody on this board will be able to help me. My deadline is fast approaching.
I am willing to share the workbook if that will help. Many thanks in advance for your expertise. You all are the best!
I am working on a decision-making tool (using Excel 2013) which will help doctors choose an appropriate drug given a patient’s symptoms and the medications he is currently taking. Cell E9 (labeled “FirstChoice” on sheet 'Recommendation') 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 (labeled "ProductsSecond") using the same above concept.
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
Here is a screen shot of the output as an example:
Recommended Pharmacologic Management | Recommended Products | |
Recommended first choice | Initiate SABA+SAMA | SABA: Proair or Proventil or Ventolin |
Alternative choice | Add LAMA, discontinue ICS | LAMA: Spiriva ICS: Aerobid |
<tbody>
</tbody>
I initially thought 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.
My son is learning Python programming and he thought that this could be accomplished in that code using the 'dictionary' function. Perhaps there's a similar function/method in VBA?
Full Disclosure: I posted this same request on another help site a couple of weeks ago, but have not received any solutions. Here's hoping that somebody on this board will be able to help me. My deadline is fast approaching.
I am willing to share the workbook if that will help. Many thanks in advance for your expertise. You all are the best!