В работе

Excel macro: analyzing a pivot table

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.

Квалификация: Excel, Visual Basic

Показать больше excel dublettes, user interface companies, starting with visual basic, need interface freelancers, market place freelancers, market place for freelancers, macro freelancers, how to market to freelancers, how to be a freelancers, how many freelancers are there, freelancers in my area, freelancers formatting, freelancers excel, excel macro how to, companies in need of freelancers, code for me freelancers, a place for freelancers, abbott, what is macro in excel, define user interface

О работодателе:
( 65 отзыв(-а, -ов) ) Leipzig, Germany

ID проекта: #5345419

9 фрилансеров(-а) в среднем готовы выполнить эту работу за $13/час


Hi, I have more than 14 years of exp and I am expert in this kind of work. I have completed more than 200 projects. Please look at the feedback left by my employer to know more about my work. Waiting for your positive Больше

$7 USD / час
(27 отзывов(-а))

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.

$15 USD / час
(5 отзывов(-а))

Dear Sir, I'm interesting in your job. I have much experience in the MS Excel and VBA. I can do this job. Regards.

$10 USD / час
(7 отзывов(-а))

VB, VBA and Databases expert for over a decade. Master in Information Systems. I have delivered similar projects in the past. Please check https://www.freelancer.gr/projects/Data-Processing-Excel/data-translation-e Больше

$10 USD / час
(5 отзывов(-а))

Dear sir, I"ve read you requirement, also check the attached carefully. With expertise in Excel, VBA, Statistics, I would be a good fit. Please see works done in my profile. My bid is $30 is for Totally completed wo Больше

$30 USD / час
(3 отзывов(-а))

Dear CS01 I just thought of sending you an alternate solution to the problem. The data volume is large, requirement is bit complex and you will need more statistical reports in future, so why dont you think of doing Больше

$8 USD / час
(3 отзывов(-а))

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.

$25 USD / час
(2 отзывов(-а))

stop faking people please stop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking peopl Больше

$5 USD / час
(0 отзывов(-а))

I have vast experience in developing VBA code. I am confident that I can provide solution to you, the way you want it as soon as possible.

$5 USD / час
(0 отзывов(-а))