Please develop 2 similar macros for analyzing a pivot table.
The table is as follows:
There are various classes of drugs (see the numbers: 3, 4, 5, ...). Inside each class, there are drug names (e.g. Accutane, Volmax, Novantrone, each in bold letters). And there are companies selling these drugs (e.g. other, Abbott, Merck).
Columns indicate if the drugs where marketed in the years 2002-2010, as shown by values of 1, 2 or 3 for each drug and each company.
What I actually want to analyze is how many entries of new companies (not the same company!) took place per drug class and year (for the years 2003-2010; 2002 is a control year to see if a new entry occurs in 2003) from the perspective of the companies being already present in the drug class. A company that is confronted with a new entry shall be listed, with the numbers of entry per drug class and year (if there is only one new entry, then 1, if two entries, then 2, etc.).
Note that one drug may have been marketed by more than one company, therefore it is necessary to look at the bold numbers in the table to see if entries took place.
Let's take class 14 as an example. There are 3 drugs marketed by three different companies. Privinil from Merck entered the class in 2003. So there should be one entry for "other" (as "other" occurs twice, it is "one company", to there should be only one entry - so please check the companies inside a class for dublettes...)
In class 15: no new entry.
In class 96, "other" is confronted with an entry in 2003 and 2010.
In class 117: no new entry.
In class 135: entry for "other" in 2003
In class 143: entry for "other" in 2010.
In class 306, Wyeth received ONE entry in 2005. Why one instead of two? The drug Ortho-Tri-Cyclen was not on the market in 2004. It seems that this is an error as the drug was on the market before! Look at class 143: Here, the break on the market was much longer, therefore, the relaunch in 2010 in class 143 was considered to be a new intro. So, please do not consider breaks of only one year as new entries.
As far as I see, these examples are all the company entries in the dataset (in the actual dataset, there are much, much more).
I would like to have a table with the results for each class next to the pivot table, giving the companies that face entry and the frequency of the entry.
The results may look like as in the example. They may also look differently (e.g. starting directly in the line where the new class starts) if this is faster for you (and cheaper for me).
I want to do an extra analysis considering only entries of so-called NCEs. This may be an extra, but very similar macro. Note that in the sample file, only one drug has NCE status = 1.
There are at least two possibilities for providing the results. First, you could provide a further line for NCE (after class > drug name > company, and then > NCE) and look where the 1 is.
Or you could use NCE instead of the counter for filling values into the table. Then every value <> 0 is an entry, with the 0s indicating that there are drugs in the corresponding years. Maybe the latter approach is easier to implement as there may be more synergies in the macro in comparison to the first.
My actual table is much larger (and, depending on the class (I have more than one type of class, i.e. not only numbers), varies in table size (lines)). So I would like to be able to define the pivot table to be analyzed via giving the cell range, and eventually give the area for the results.
As I have several other definitions of classes (not all are numbers), it may be possible to recognize classes in the macro via the formatting of the cells (as may be useful for detecting drugs and companies).
I also don't need a user interface. I am fine with the plain code and some comments how to adjust cell ranges, etc.
In case of doubt, drop me a line, please.
Only freelancers will be considered that give an estimate how much time they will need for completing the project.
9 фрилансеров(-а) в среднем готовы выполнить эту работу за $13/час
Prince 2 certified IT Project Manager, MBA with experience in website admin, website management, testing, Quality control etc. Excerpts from previous test reports on request.
Hi I am work on one of the biggest Brazilian banks, using Excel all the time, so I can help you with your task. Please feel free to check my profile and message me if you want.