To start, my apologies if this makes no sense. Explaining this is a little tough but I have a feeling there's a way to do it. I can try to better explain if anyone needs clarification.
I work in a hospital pharmacy and am trying to simplify a job process for our secretary. Basically this job duty for her is to go through the invoices and separate the "chemo" drugs out and provide a total cost just for those drugs. This same process needs to be done for a few different classes of drugs but I can take care of the rest once I have a formula.
I'm trying to make an excel document that allows her to paste the entire contents of the CSV version of the invoice (downloaded from the wholesaler) into the main tab labeled "Paste Here". I want the second tab, labeled "Results", to give a count for all drugs of each class (chemo in this case), as well as a total cost. The third tab is labeled "Sorting Data" and contains all of the drugs that need to be segregated from the invoice.
My main goal is to have a formula located in cell B3 in the "Results" tab to look through cells J17 - J82 on the "paste here tab", and compare all values with cells A2 - A86 on the "Sorting Data" tab. If there are matches I want it to sum up all of the costs associated with those medications, and the costs are located in cells P17-P82 on the "Paste Here" tab. For example, if two different drugs are on the chemo list within the invoice I want just those two costs totaled up. Same thing if there are more.
I understand that I would have to make adjustments to the ranges to accompany larger invoices, which I'm comfortable doing, I just can't seem to wrap my head around the logic. I have a sample document attached, with the areas in question all highlighted. I can duplicate this formula, if there actually is one, and make it work for the other fields on my own.
Many thanks to anyone who feels they're up for this task! It would help me greatly, as well as help me learn how to do more advanced formulas on here.
Bookmarks